﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 01.02.2014*/
/**/


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BalConSetup' AND COLUMN_NAME = 'SolduriInitialeDefalcateJurnale')
BEGIN
	ALTER TABLE dbo.BalConSetup ADD SolduriInitialeDefalcateJurnale bit DEFAULT(0) NOT NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_CopiazaSolduriInitialeCapitole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_CopiazaSolduriInitialeCapitole]
GO


CREATE PROCEDURE [dbo].[balcon_CopiazaSolduriInitialeCapitole]
	@CodUnitate smallint,
	@jurnalSelectat int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DISABLE TRIGGER [UpdateParinteJurnaleSolduriInitiale] ON [JurnaleSolduriInitiale];

	INSERT INTO [JurnaleSolduriInitiale]
           ([IDJurnal]
           ,[Cont]
           ,[CodUnitate]
           ,[DebitInitial]
           ,[CreditInital]
           ,[DebitPrecedent]
           ,[CreditPrecedent])
     SELECT @jurnalSelectat,Cont,CodUnitate,DebitInitial,CreditInitial,DebitPrecedent,CreditPrecedent
           FROM Conturi WHERE CodUnitate = @CodUnitate AND Terminal=1;

	 ENABLE TRIGGER  [UpdateParinteJurnaleSolduriInitiale] ON [JurnaleSolduriInitiale]
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_UpdateSolduriInitialeJurnale]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_UpdateSolduriInitialeJurnale]
GO

CREATE PROCEDURE [dbo].[balcon_UpdateSolduriInitialeJurnale]
	@CodUnitate smallint, 
	@Cont dbo.TipCont,
	@IDJurnal int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--pentru cazul in care se sterg toate analiticele se pune pe zero
	UPDATE cnt SET cnt.DebitInitial = 0,cnt.CreditInitial = 0
	FROM Conturi cnt
	WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate

	--modifica contul suma din tabela conturi

	UPDATE cnt SET cnt.DebitInitial = 
	CASE 
		WHEN Tip='A' THEN jsip.DebitInitial 
		WHEN Tip='P' THEN 0 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN jsip.DebitInitial-jsip.CreditInitial 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN 0
		ELSE 0
	END,
	cnt.CreditInitial = 
	CASE 
		WHEN Tip='A' THEN 0 
		WHEN Tip='P' THEN jsip.CreditInitial
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN 0 
		WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN jsip.CreditInitial - jsip.DebitInitial
		ELSE 0
	END
	FROM Conturi cnt, 
	(SELECT COALESCE(SUM(a.DebitInitial),0) DebitInitial, COALESCE(SUM(a.CreditInital),0) CreditInitial, Cont, CodUnitate FROM JurnaleSolduriInitiale a 
	 WHERE a.Cont = @Cont AND CodUnitate = @CodUnitate 
	 GROUP BY Cont, CodUnitate) jsip
	WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate 

	SELECT @Cont = ContParinte FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
	
	WHILE @Cont <> 'N/A'
	BEGIN
		--adauga parintele pentru jurnalul respectiv
		--IF NOT EXISTS(SELECT * FROM JurnaleSolduriInitiale a WHERE a.Cont=@Cont AND a.IDJurnal=@IDJurnal AND a.CodUnitate=@CodUnitate)
			--INSERT INTO JurnaleSolduriInitiale ([IDJurnal],[Cont],[CodUnitate],[DebitInitial],[CreditInital],[DebitPrecedent],[CreditPrecedent])
			--VALUES (@IDJurnal,@Cont,@CodUnitate,0,0,0,0)
	
	    --pune pe zero in cazul stergerii
		UPDATE jsi SET jsi.DebitInitial = 0,jsi.CreditInital = 0
		FROM JurnaleSolduriInitiale jsi
		WHERE jsi.Cont = @Cont AND jsi.CodUnitate = @CodUnitate AND jsi.IDJurnal = @IDJurnal
		
		--modifica soldul initial al contului parinte din solduri initiale jurnale
		UPDATE jsi SET jsi.DebitInitial = jsip.DebitInitial,jsi.CreditInital = jsip.CreditInitial
		FROM JurnaleSolduriInitiale jsi, 
		(SELECT COALESCE(SUM(a.DebitInitial),0) DebitInitial, COALESCE(SUM(a.CreditInital),0) CreditInitial, ContParinte, a.CodUnitate,a.IDJurnal  FROM JurnaleSolduriInitiale a 
		 JOIN Conturi c ON a.Cont = c.Cont AND a.CodUnitate = c.CodUnitate
		 WHERE c.ContParinte = @Cont AND a.CodUnitate = @CodUnitate AND a.IDJurnal=@IDJurnal
		 GROUP BY c.ContParinte, a.CodUnitate,a.IDJurnal ) jsip
		WHERE jsi.Cont = @Cont AND jsi.CodUnitate = @CodUnitate AND jsi.IDJurnal = jsip.IDJurnal
	
		--pentru cazul in care se sterg toate analiticele se pune pe zero
		UPDATE cnt SET cnt.DebitInitial = 0,cnt.CreditInitial = 0
		FROM Conturi cnt
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate

		--modifica soldurile initiale parinte din tabela conturi
		UPDATE cnt SET
		 cnt.DebitInitial = 
			CASE 
				WHEN Tip='A' THEN jsip.DebitInitial 
				WHEN Tip='P' THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN jsip.DebitInitial-jsip.CreditInitial 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN 0
				ELSE 0
			END,
			cnt.CreditInitial = 
			CASE 
				WHEN Tip='A' THEN 0 
				WHEN Tip='P' THEN jsip.CreditInitial
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN jsip.CreditInitial - jsip.DebitInitial
				ELSE 0
			END
		FROM Conturi cnt, 
		(SELECT COALESCE(SUM(DebitInitial),0) DebitInitial, COALESCE(SUM(CreditInitial),0) CreditInitial, ContParinte,CodUnitate FROM Conturi c 
		 WHERE ContParinte = @Cont AND CodUnitate = @CodUnitate
		 GROUP BY ContParinte,CodUnitate) jsip
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate
		
		
		SELECT @Cont = ContParinte FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
		
	END
	

END

GO


IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateParinteJurnaleSolduriInitiale]'))
DROP TRIGGER [dbo].[UpdateParinteJurnaleSolduriInitiale]
GO


CREATE TRIGGER [dbo].[UpdateParinteJurnaleSolduriInitiale] 
	ON [dbo].[JurnaleSolduriInitiale]
   AFTER  INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Cont dbo.TipCont
	DECLARE @IDJurnal int
	DECLARE @CodUnitate smallint

    DECLARE update_cursor CURSOR FOR  SELECT DISTINCT Cont,IDJurnal,CodUnitate FROM (SELECT * FROM inserted UNION SELECT * FROM deleted) crt
 	OPEN update_cursor
 	FETCH NEXT FROM update_cursor INTO @Cont,@IDJurnal,@CodUnitate
 	WHILE (@@FETCH_STATUS <> -1)
 	BEGIN
 		IF (@@FETCH_STATUS <> -2)
   		BEGIN   
   			EXEC balcon_UpdateSolduriInitialeJurnale @CodUnitate,@Cont,@IDJurnal
   		END
    	FETCH NEXT FROM update_cursor INTO @Cont,@IDJurnal,@CodUnitate
 	END
 	CLOSE update_cursor
 	DEALLOCATE update_cursor

END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SectorBugetar]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[SectorBugetar](
		[IDSectorBugetar] [smallint] IDENTITY(0,1) NOT NULL,
		[Cod] [varchar](10) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
	 CONSTRAINT [PK_SectorBugetar] PRIMARY KEY CLUSTERED ([IDSectorBugetar] ASC)
	)
	SET IDENTITY_INSERT [SectorBugetar] ON
	INSERT INTO [SectorBugetar] ([IDSectorBugetar],[Cod],[Denumire])VALUES(0,'01','Bugetul de stat ( administratie centrala)')
	INSERT INTO [SectorBugetar] ([IDSectorBugetar],[Cod],[Denumire])VALUES(1,'02','Bugetul local ( administratie locala)')
	INSERT INTO [SectorBugetar] ([IDSectorBugetar],[Cod],[Denumire])VALUES(2,'03','Bugetul asigurarilor sociale de stat')
	INSERT INTO [SectorBugetar] ([IDSectorBugetar],[Cod],[Denumire])VALUES(3,'04','Bugetul fondului de somaj')
	INSERT INTO [SectorBugetar] ([IDSectorBugetar],[Cod],[Denumire])VALUES(4,'05','Bugetul fondului de sanatate (FNUASS)')
	SET IDENTITY_INSERT [SectorBugetar] OFF

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RulajeDoc' AND COLUMN_NAME = 'IDSectorBugetar')
BEGIN
	ALTER TABLE dbo.RulajeDoc ADD IDSectorBugetar smallint NULL
	ALTER TABLE dbo.RulajeDoc ADD CONSTRAINT
	FK_RulajeDoc_SectorBugetar FOREIGN KEY
	(
	IDSectorBugetar
	) REFERENCES dbo.[SectorBugetar]
	(
	[IDSectorBugetar]
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'arhRulajeDoc' AND COLUMN_NAME = 'IDSectorBugetar')
BEGIN
	ALTER TABLE dbo.arhRulajeDoc ADD IDSectorBugetar smallint NULL

END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BalConSetup' AND COLUMN_NAME = 'FolosesteSectorBugetar')
BEGIN
	ALTER TABLE dbo.BalConSetup ADD FolosesteSectorBugetar bit NOT NULL DEFAULT(0)

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BalConSetup' AND COLUMN_NAME = 'SectorBugetarImplicit')
BEGIN
	ALTER TABLE dbo.BalConSetup ADD SectorBugetarImplicit smallint NULL

END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959' AND [HandlerName]='Sector_Bugetar'
IF @Cate= 0 
BEGIN
	SET IDENTITY_INSERT [admin_ApplicationMenu] ON
	INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9014,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Sector Bugetar',9006,110,'Sector_Bugetar',NULL,NULL)
	SET IDENTITY_INSERT [admin_ApplicationMenu] OFF
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaTGC2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaTGC2014]
GO


CREATE PROCEDURE [dbo].[balcon_GetBalantaTGC2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM (SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],sum(a.[DebitPrecedent]) DebitPrecedent ,sum(a.[CreditPrecedent]) CreditPrecedent,
					sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
					FROM [Conturi] a JOIN [Conturi] b
					ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
					where a.[CodUnitate]=@CodUnitate and a.categorie=3 and b.ContParinte='N/A'
					group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue]
					UNION
					SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[SortValue],a2.[DebitPrecedent],a2.[CreditPrecedent],
						a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
					WHERE a2.codunitate=@CodUnitate and a2.categorie=0 
				 ) c  LEFT OUTER JOIN 			
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie in (@pCategorie,3) 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiTGC)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END


GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaArh2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaArh2014]
GO

CREATE PROCEDURE [dbo].[balcon_GetBalantaArh2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@pAn smallint	
AS
BEGIN
	SET NOCOUNT ON;	

	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [arhConturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND c.[An]=@pAn
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			arhRulaje rj 
			JOIN  arhRulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate AND rjd.An = @pAn
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_UpdateSolduriInitialeConturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_UpdateSolduriInitialeConturi]
GO

CREATE PROCEDURE [dbo].[balcon_UpdateSolduriInitialeConturi]
	@CodUnitate smallint, 
	@Cont dbo.TipCont
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT @Cont = COALESCE(ContParinte,'N/A') FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
	
	WHILE @Cont <> 'N/A'
	BEGIN
		--adauga parintele pentru jurnalul respectiv
		--IF NOT EXISTS(SELECT * FROM JurnaleSolduriInitiale a WHERE a.Cont=@Cont AND a.IDJurnal=@IDJurnal AND a.CodUnitate=@CodUnitate)
			--INSERT INTO JurnaleSolduriInitiale ([IDJurnal],[Cont],[CodUnitate],[DebitInitial],[CreditInital],[DebitPrecedent],[CreditPrecedent])
			--VALUES (@IDJurnal,@Cont,@CodUnitate,0,0,0,0) 
	
		--pentru cazul in care se sterg toate analiticele se pune pe zero
		UPDATE cnt SET cnt.DebitInitial = 0,cnt.CreditInitial = 0
		FROM Conturi cnt
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate

		--modifica soldurile initiale parinte din tabela conturi
		UPDATE cnt SET
		 cnt.DebitInitial = 
			CASE 
				WHEN Tip='A' THEN jsip.DebitInitial 
				WHEN Tip='P' THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN jsip.DebitInitial-jsip.CreditInitial 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN 0
				ELSE 0
			END,
			cnt.CreditInitial = 
			CASE 
				WHEN Tip='A' THEN 0 
				WHEN Tip='P' THEN jsip.CreditInitial
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial>0 THEN 0 
				WHEN Tip='B' AND jsip.DebitInitial-jsip.CreditInitial<0 THEN jsip.CreditInitial - jsip.DebitInitial
				ELSE 0
			END
		FROM Conturi cnt, 
		(SELECT COALESCE(SUM(DebitInitial),0) DebitInitial, COALESCE(SUM(CreditInitial),0) CreditInitial, ContParinte,CodUnitate FROM Conturi c 
		 WHERE ContParinte = @Cont AND CodUnitate = @CodUnitate
		 GROUP BY ContParinte,CodUnitate) jsip
		WHERE cnt.Cont = @Cont AND cnt.CodUnitate = @CodUnitate
		
		
		SELECT @Cont = ContParinte FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @Cont
		
	END
	

END

GO



IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ActSolduri]'))
DROP TRIGGER [dbo].[ActSolduri]
GO

CREATE TRIGGER [dbo].[ActSolduri] ON [dbo].[Conturi] 
FOR INSERT, UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @Cont AS varchar(50)
DECLARE @ContParinte AS varchar(50)
DECLARE @CodUnitate AS smallint

SELECT @Cont = [Cont],@CodUnitate=[CodUnitate],@ContParinte=[ContParinte] FROM inserted
IF UPDATE(ContParinte)
BEGIN
	UPDATE [Conturi] SET Terminal=0 FROM inserted i WHERE [Conturi].[Cont] = i.[ContParinte] AND [Conturi].[CodUnitate]=i.[CodUnitate] 
		AND ([Conturi].[Cont]<>'N/A')
END
IF UPDATE(CreditInitial) OR UPDATE(DebitInitial) 
BEGIN
	SET NOCOUNT ON;
    DECLARE update_cursor_cont CURSOR FOR  SELECT DISTINCT Cont,CodUnitate FROM (SELECT * FROM inserted) crt
 	OPEN update_cursor_cont
 	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate
 	WHILE (@@FETCH_STATUS <> -1)
 	BEGIN
 		IF (@@FETCH_STATUS <> -2)
   		BEGIN   
   			EXEC balcon_UpdateSolduriInitialeConturi @CodUnitate,@Cont
   		END
    	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate
 	END
 	CLOSE update_cursor_cont
 	DEALLOCATE update_cursor_cont
		 
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
	SET NOCOUNT ON;
    DECLARE update_cursor_cont CURSOR FOR  SELECT DISTINCT Cont,CodUnitate,ContParinte FROM (SELECT * FROM deleted) crt
 	OPEN update_cursor_cont
 	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate,@ContParinte
 	WHILE (@@FETCH_STATUS <> -1)
 	BEGIN
 		IF (@@FETCH_STATUS <> -2)
   		BEGIN   
   			IF NOT EXISTS(SELECT * FROM Conturi WHERE CodUnitate=@CodUnitate AND ContParinte=@ContParinte)
   			BEGIN
   				UPDATE Conturi SET DebitInitial=0,CreditInitial=0 WHERE CodUnitate=@CodUnitate AND Cont=@ContParinte
   			END	
   			EXEC balcon_UpdateSolduriInitialeConturi @CodUnitate,@ContParinte
   		END
    	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate,@ContParinte
 	END
 	CLOSE update_cursor_cont
 	DEALLOCATE update_cursor_cont
END
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_StergeSolduriInitialeCapitoleZero]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_StergeSolduriInitialeCapitoleZero]
GO


CREATE PROCEDURE [dbo].[balcon_StergeSolduriInitialeCapitoleZero]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DISABLE TRIGGER [UpdateParinteJurnaleSolduriInitiale] ON [JurnaleSolduriInitiale];

	DELETE FROM [JurnaleSolduriInitiale] WHERE DebitInitial=0 AND CreditInital=0 AND DebitPrecedent=0 AND CreditPrecedent=0;

	ENABLE TRIGGER  [UpdateParinteJurnaleSolduriInitiale] ON [JurnaleSolduriInitiale]
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaExtinsa2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaExtinsa2014]
GO

CREATE PROCEDURE [dbo].[balcon_GetBalantaExtinsa2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



--  SUBSTRING(c.Cont,1,7) + COALESCE(SursaFinantare,'') + COALESCE(SectorBugetar,'') + SUBSTRING(c.Cont,8,50)
	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT   SUBSTRING(c.Cont,1,7) + CASE WHEN LEN(c.Cont)>=7 THEN COALESCE(SursaFinantare,'') + COALESCE(SectorBugetar,'') +REPLACE(COALESCE(CodJurnal,''),'.','') ELSE '' END + SUBSTRING(c.Cont,8,50) Cont, 
		    c.Cont ContOriginal,
		    C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal,COALESCE(SursaFinantare,'') SursaFinantare,COALESCE(SectorBugetar,'') SectorBugetar,Coalesce(CodJurnal,'') CodJurnal   FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal, COALESCE(G.Cod,'') SursaFinantare,COALESCE(H.Cod,'') SectorBugetar,COALESCE(I.Codificare,'') CodJurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							LEFT OUTER JOIN SurseFinantare G ON G.IDSursaFinantare = D.IDSursaFinantare
							LEFT OUTER JOIN SectorBugetar H ON H.IDSectorBugetar = D.IDSectorBugetar
							LEFT OUTER JOIN Jurnale I ON I.IDJurnal = D.IDJurnal
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal,G.Cod,H.Cod,I.Codificare
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal , COALESCE(G.Cod,'') SursaFinantare,COALESCE(H.Cod,'') SectorBugetar,COALESCE(I.Codificare,'') CodJurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							LEFT OUTER JOIN SurseFinantare G ON G.IDSursaFinantare = D.IDSursaFinantare
							LEFT OUTER JOIN SectorBugetar H ON H.IDSectorBugetar = D.IDSectorBugetar							
							LEFT OUTER JOIN Jurnale I ON I.IDJurnal = D.IDJurnal
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal,G.Cod,H.Cod,I.Codificare
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal,SursaFinantare,SectorBugetar,CodJurnal
				) RUL ON  dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic,CASE WHEN LEN(c.Cont)>=7 THEN COALESCE(SursaFinantare,'') + COALESCE(SectorBugetar,'') +REPLACE(COALESCE(CodJurnal,''),'.','') ELSE '' END
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE ContOriginal + '%') OR (ContCredit LIKE ContOriginal + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0  AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END


GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BalConSetup' AND COLUMN_NAME = 'NivelAfisareConturi')
BEGIN
	ALTER TABLE dbo.BalConSetup ADD NivelAfisareConturi smallint NOT NULL DEFAULT 0

END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhJurnale]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[arhJurnale](
	[IDJurnal] [int] NOT NULL,
	[Denumire] [varchar](100) NOT NULL,
	[IDParinte] [int] NOT NULL,
	[Codificare] [varchar](50) NOT NULL,
	[Terminal] [bit] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[IBAN] [varchar](24) NULL,
	[IDBanca] [smallint] NULL,
	[An] smallint NOT NULL,
 CONSTRAINT [PK_arhJurnale] PRIMARY KEY CLUSTERED ([IDJurnal] ASC,[An] Asc)
)
END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhBanci]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[arhBanci](
	[IdBanca] [smallint] NOT NULL,
	[Denumire] [varchar](100) NOT NULL,
	[BIC] [varchar](4) NOT NULL,
	[An] smallint NOT NULL,
 CONSTRAINT [PK_arhBanci] PRIMARY KEY CLUSTERED ([IdBanca] ASC,[An] ASC )
) 
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhParteneri]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[arhParteneri](
	[IDPartener] [bigint] NOT NULL,
	[CUI] [decimal](13, 0) NOT NULL,
	[Denumire] [varchar](200) NOT NULL,
	[IDBanca] [smallint] NOT NULL,
	[ContBancar] [varchar](50) NOT NULL,
	[Cont] [varchar](50) NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[TipPartener] [varchar](50) NOT NULL,
	[IDJudet] [int] NOT NULL,
	[IDLocalitate] [int] NOT NULL,
	[Strada] [varchar](50) NULL,
	[Nr] [varchar](10) NULL,
	[AtributFiscal] [varchar](10) NOT NULL,
	[JudetRegComert] [smallint] NOT NULL,
	[NrRegComert] [int] NOT NULL,
	[AnRegComert] [smallint] NOT NULL,
	[NumeDelegat] [varchar](50) NOT NULL,
	[SerieAI] [varchar](5) NOT NULL,
	[NrAI] [varchar](6) NOT NULL,
	[MijlocTransport] [varchar](50) NOT NULL,
	[NrMijlocTransport] [varchar](50) NOT NULL,
	[CodPostal] [varchar](10) NULL,
	[Telefon] [varchar](50) NULL,
	[Fax] [varchar](50) NULL,
	[ContClient] [dbo].[TipCont] NULL,
	[ConditiiDePlata] [varchar](250) NULL,
	[IDTara] [int] NULL,
	[IDContactDelegat] [int] NULL,
	[IDMijlocTransport] [smallint] NULL,
	[ScadentaFacturiClient] [int] NULL,
	[ScadentaFacturiFurnizor] [int] NULL,
	[ScadentaFacturiSfarsitLuna] [bit] NOT NULL,
	[An] smallint NOT NULL,
 CONSTRAINT [PK_arhFurnizori] PRIMARY KEY CLUSTERED ([IDPartener] ASC,[An] ASC)
)
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhConturiSpeciale]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[arhConturiSpeciale](
	[Cont] [varchar](50) NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[Tipuri] [varchar](50) NOT NULL,
	[Descriere] [varchar](50) NOT NULL,
	[An] [smallint] NOT NULL,
 CONSTRAINT [PK_arhConturiSpeciale] PRIMARY KEY CLUSTERED 
(
	[Cont] ASC,
	[CodUnitate] ASC,
	[An] ASC
)
)
END
GO







IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_SchimbaCodUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_SchimbaCodUnitate]
GO


CREATE PROCEDURE [dbo].[balcon_SchimbaCodUnitate]  
@oldCodUnit as smallint,
@newCodUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)



BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------

ALTER TABLE dbo.Ordonantari DROP CONSTRAINT FK_Ordonantari_ArticoleAliniate
IF @@ERROR != 0 
	GOTO EROARE
ALTER TABLE dbo.JurnaleSolduriInitiale DROP CONSTRAINT FK_JurnaleSolduriInitiale_Conturi
IF @@ERROR != 0 
	GOTO EROARE
ALTER TABLE dbo.ConturiSpeciale DROP CONSTRAINT FK_ConturiSpeciale_Conturi
IF @@ERROR != 0 
	GOTO EROARE




DELETE FROM [ArticoleAliniate] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [ArticoleAliniate] SET [CodUnitate]=@newCodUnit
WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Bilant')
--Bilant---------------------------------------
DELETE FROM [Bilant] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 GOTO EROARE

UPDATE [Bilant] SET CodUnitate=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('Sterge rulaje')
DELETE FROM [Rulaje] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 
	GOTO EROARE
	
PRINT('Sterge rulajeDoc')
DELETE FROM [RulajeDoc] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi')
--Conturi---------------------------------------


DELETE FROM [Conturi] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Conturi] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
DELETE FROM [ConturiValuta] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [ConturiValuta] SET CodUnitate=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
DELETE FROM [JurnaleSolduriInitiale] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [JurnaleSolduriInitiale] SET [CodUnitate]=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('Jurnale')
DELETE FROM [Jurnale] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Jurnale] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Parteneri')
DELETE FROM [Parteneri] WHERE [CodUnitate]=@newCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Parteneri] SET [CodUnitate]=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Ordonantari')

DELETE FROM [Ordonantari]  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

UPDATE [Ordonantari] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
DELETE FROM [RezultatPatrimonial] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [RezultatPatrimonial] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
PRINT(@Comanda)
IF @@ERROR != 0 
	GOTO EROARE

PRINT('RulajeDoc')
--RulajeDoc---------------------------------------

UPDATE [RulajeDoc] SET 
	  [CodUnitate]=@newCodUnit 
	  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Rulaje')
--Rulaje---------------------------------------

UPDATE [Rulaje] SET [CodUnitate]=@newCodUnit
	  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
DELETE FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE	

UPDATE [VenituriSiCheltuieli] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE	

ALTER TABLE dbo.Ordonantari ADD CONSTRAINT
	FK_Ordonantari_ArticoleAliniate FOREIGN KEY (ArtAl,CodUnitate) REFERENCES dbo.ArticoleAliniate (ArtAl,CodUnitate)
	ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	

ALTER TABLE dbo.JurnaleSolduriInitiale ADD CONSTRAINT
	FK_JurnaleSolduriInitiale_Conturi FOREIGN KEY
	(Cont,CodUnitate) REFERENCES dbo.Conturi (Cont,CodUnitate) 
	ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	
	
ALTER TABLE dbo.ConturiSpeciale ADD CONSTRAINT
	FK_ConturiSpeciale_Conturi FOREIGN KEY
	(Cont,CodUnitate) REFERENCES dbo.Conturi(Cont,CodUnitate) ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	


COMMIT




GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la schimbare codului unitatii!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:






END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IsContInclus]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IsContInclus]
GO

CREATE FUNCTION [dbo].[IsContInclus] (@SursaParam TipCont, @DestinatieParam TipCont, @TerminalSursa bit)  
RETURNS bit AS  
BEGIN 
	DECLARE @inclus bit
	DECLARE @terminal bit
	DECLARE @Sursa TipCont
	DECLARE @Destinatie TipCont
	
	SET @Sursa = LTRIM(RTRIM(@SursaParam))
	SET @Destinatie = LTRIM(RTRIM(@destinatieParam))
	
	SET @terminal = 1
	SET @inclus = 0
	-- daca sunt egale e ok
	IF (@Destinatie = @Sursa)
		RETURN 1
	IF (substring(@Destinatie,1,len(@Sursa))=@Sursa) AND (@TerminalSursa=0)
		SET @inclus = 1
		
	IF LEN(@Destinatie) > LEN(@Sursa)
	BEGIN
		DECLARE @pos2 int
		SET @pos2 = CHARINDEX('.',@Sursa) 
		IF  @pos2<>0
		BEGIN		
			DECLARE @ch  varchar(1)
			SET @ch = substring(@Destinatie,LEN(@Sursa)+1,1)  
			IF @ch<>'.'
				SET @terminal = 0
		END
		
	END
	IF (@inclus=1) AND (@terminal=1)
		RETURN  1
	RETURN 0
END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FolosesteDataExtras')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD FolosesteDataExtras bit DEFAULT(0) NOT NULL

END
GO

 UPDATE PlanConturi SET ContParinte='N/A' WHERE Cont in ('681','686','688','771','781','786','788')        
 GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_PopuleazaConturiUnitateNoua]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_PopuleazaConturiUnitateNoua]
GO


-- =============================================
-- Author:		Popa Dorin
-- Create date: 01.03.2014
-- Description:	Populeaza tabela de conturi pentru o unitate noua
-- =============================================
CREATE PROCEDURE [dbo].[balcon_PopuleazaConturiUnitateNoua]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @DataPlanConturi datetime
	SET NOCOUNT ON;
	BEGIN TRAN
	
	SET @DataPlanConturi = '2000-1-1';
	
	SELECT TOP 1 @DataPlanConturi =MIN(DataAplicare)  FROM PlanConturi pc
	JOIN [BalconSetup] b On b.CodUnitate = @CodUnitate AND pc.TipUnitate=b.TipUnitate 
	Where YEAR(DataAplicare)*100 + MONTH(DataAplicare) >= b.AnCurent*100+b.LunaCurenta
	

	PRINT('NETERMINALE')
    --copiaza conturile neterminale
	INSERT INTO [Conturi]([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie],[CodUnitate],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[RulajDebit] ,[RulajCredit])
         SELECT DISTINCT [Cont],[Denumire],[Tip],[Terminal],[ContParinte],0,[Categorie],@CodUnitate,0,0,0,0,0,0 FROM [PlanConturi] pc
         JOIN BalconSetup b ON [DataAplicare]=@DataPlanConturi AND b.CodUnitate = @CodUnitate AND pc.[TipUnitate]=b.TipUnitate                 
         WHERE  [Preluare] = 1 AND Terminal=0  AND Categorie=0
         ORDER BY [Cont]
	IF @@ERROR != 0 GOTO EROARE	

	PRINT('TERMINALE')
	--copiaza conturile terminale
	INSERT INTO [Conturi]([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie],[CodUnitate],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[RulajDebit] ,[RulajCredit])
		SELECT DISTINCT [Cont],[Denumire],[Tip],[Terminal],[ContParinte],0,[Categorie],@CodUnitate,0,0,0,0,0,0 FROM [PlanConturi] pc
         JOIN BalconSetup b ON [DataAplicare]=@DataPlanConturi AND b.CodUnitate = @CodUnitate AND pc.[TipUnitate]=b.TipUnitate                 
         WHERE  [Preluare] = 1 AND Terminal=1 AND Categorie=0
         ORDER BY [Cont]
    IF @@ERROR != 0 GOTO EROARE					
		
	COMMIT

	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la balcon_PopuleazaConturiUnitateNoua!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:	
	
END


GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreazaArhiva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreazaArhiva]
GO



CREATE PROCEDURE [dbo].[CreazaArhiva]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strContAn varchar(40)
	DECLARE @Comanda varchar(3000)
	DECLARE @existTableCont bit
	
	SET @strContAn = 'arhConturi'
	SET @existTableCont = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContAn)
		SET @existTableCont = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [' + @strContAn + '] (
		[Cont] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
		[Denumire] [varchar](200) NOT NULL DEFAULT (''''),
		[Tip] [char](1) NOT NULL DEFAULT (''A''),
		[Terminal] [bit] NOT NULL DEFAULT ((1)),
		[ContParinte] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[EsteAnalitic] [bit] NOT NULL DEFAULT ((1)),
		[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Categorie] [tinyint] NOT NULL DEFAULT (0),
		[RulajDebit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[RulajCredit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Sold] [decimal](20, 2) NOT NULL DEFAULT ((0)),
		[SortValue] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContAn +'] PRIMARY KEY CLUSTERED([Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContAn +'_' + @strContAn + '] FOREIGN KEY([ContParinte], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strContAn+'_Tip] CHECK  (([Tip]=''A'' OR [Tip]=''P'' OR [Tip]=''B''))
	)'
	
	IF @existTableCont = 0
		EXEC(@Comanda)


	DECLARE @strAAAn varchar(40)
	DECLARE @existTableAA bit
	
	SET @strAAAn = 'arhArticoleAliniate' 
	SET @existTableAA = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strAAAn)
		SET @existTableAA = 1

	SET @Comanda = 'CREATE TABLE ['+@strAAAn+'](
	[ArtAl] [varchar](40) NOT NULL,
	[Denumire] [varchar](200) NOT NULL,
	[AAParinte] [varchar](40) NOT NULL,
	[Terminal] [bit] NOT NULL,
	[Titlu] [varchar](100) NOT NULL,
	[Categorii] [varchar](50) NOT NULL,
	[TermDenumire] [varchar](100) NOT NULL,
	[StartDenumire] [varchar](100) NOT NULL,
	[Anexa6] [bit] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[ExecutieCheltuieli] [bit] NOT NULL DEFAULT(0),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strAAAn+'] PRIMARY KEY CLUSTERED([ArtAl] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strAAAn+'_'+@strAAAn+'] FOREIGN KEY([AAParinte], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strAAAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableAA = 0
		EXEC(@Comanda)

	---Bilant----------------------------
	DECLARE @strBilantAn varchar(40)
	DECLARE @existTableBilant bit
	
	SET @strBilantAn = 'arhBilant'
	SET @existTableBilant = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strBilantAn)
		SET @existTableBilant = 1

	SET @Comanda = 'CREATE TABLE ['+@strBilantAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strBilantAn+'] PRIMARY KEY CLUSTERED([row] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strBilantAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableBilant = 0
		EXEC(@Comanda)
	---Conturi Valuta--------------------
	
	DECLARE @strContValutaAn varchar(40)
	DECLARE @existTableContValuta bit
	
	SET @strContValutaAn = 'arhConturiValuta'
	SET @existTableContValuta = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContValutaAn)
		SET @existTableContValuta = 1

	SET @Comanda = 'CREATE TABLE ['+@strContValutaAn+'](
	[CodValuta] [smallint] NOT NULL DEFAULT ((0)),
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[IdJurnal] [int] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContValutaAn+'] PRIMARY KEY CLUSTERED([CodValuta] ASC,[Cont] ASC,[CodUnitate] ASC,[IdJurnal] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContValutaAn+'_'+@strContAn+'] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Jurnale] FOREIGN KEY([IdJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableContValuta = 0
		EXEC(@Comanda)

	---Jurnale Solduri Initiale--------------------
	
	DECLARE @strJurnaleSIAn varchar(40)
	DECLARE @existTableJurnaleSI bit
	
	SET @strJurnaleSIAn = 'arhJurnaleSolduriInitiale'
	SET @existTableJurnaleSI = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strJurnaleSIAn)
		SET @existTableJurnaleSI = 1

	SET @Comanda = 'CREATE TABLE ['+@strJurnaleSIAn+'](
	[IDJurnal] [int] NOT NULL,
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[CreditInital] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strJurnaleSIAn+'] PRIMARY KEY CLUSTERED ([IDJurnal] ASC,[Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_' + @strContAn + '] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES [' + @strContAn + '] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate])
	)'
	
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)


	---Ordonantari--------------------
	
	DECLARE @strOrdonantariAn varchar(40)
	DECLARE @existTableOrdonantari bit
	
	SET @strOrdonantariAn = 'arhOrdonantari'
	SET @existTableOrdonantari = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strOrdonantariAn)
		SET @existTableOrdonantari = 1

	SET @Comanda = 'CREATE TABLE ['+@strOrdonantariAn+'](
	[NrDoc] [decimal](18, 0) NOT NULL,
	[DataDoc] [datetime] NOT NULL,
	[IDFurnizor] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[IDJurnal] [int] NOT NULL,
	[Scop] [varchar](100) NOT NULL,
	[DocJust] [varchar](200) NOT NULL,
	[AngLegal] [varchar](50) NOT NULL,
	[Achitat] [bit] NOT NULL,
	[IdOrdonantare] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT 0,
	[IdModPlata] [int] NOT NULL DEFAULT 1,
	[TotalDatorat] [decimal](18, 2) NOT NULL DEFAULT 0,
	[TotalAvansuri] [decimal](18, 2) NOT NULL DEFAULT 0,
	[ArtAl] [varchar](40) NOT NULL DEFAULT '''',
	[VizatCFP] [bit] NOT NULL,
	[MotivRefuz] [varchar](250) NOT NULL DEFAULT '''',
	[IDResponsabil] [int] NOT NULL DEFAULT 0,
	[DataVizaCFP] [datetime] NOT NULL DEFAULT (getdate()),
	[NrRegCFP] [varchar](20) NOT NULL DEFAULT (''FARA''),
	[IBANSursa] [varchar](24) NULL,
	[IBANDest] [varchar](24) NULL,
	[IDDocumentPlata] [int] NULL,
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strOrdonantariAn+'] PRIMARY KEY CLUSTERED ([IdOrdonantare] ASC),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_'+@strAAAn+'] FOREIGN KEY([ArtAl], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Parteneri] FOREIGN KEY([IDfurnizor]) REFERENCES [dbo].[Parteneri] ([IdPartener]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_ModPlata] FOREIGN KEY([IdModPlata]) REFERENCES [dbo].[ModPlata] ([IDModPlata]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])	
	)'
	IF @existTableOrdonantari = 0
		EXEC(@Comanda)

	
	---RezultatPatrimonial--------------------
	
	DECLARE @strRezultatPatrimonialAn varchar(40)
	DECLARE @existTableRezultatPatrimonial bit
	
	SET @strRezultatPatrimonialAn = 'arhRezultatPatrimonial'
	SET @existTableRezultatPatrimonial = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRezultatPatrimonialAn)
		SET @existTableRezultatPatrimonial = 1

	SET @Comanda = 'CREATE TABLE ['+@strRezultatPatrimonialAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRezultatPatrimonialAn+'] PRIMARY KEY CLUSTERED ([row] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strRezultatPatrimonialAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)

	---RulajeDoc--------------------
	
	DECLARE @strRulajeDocAn varchar(40)
	DECLARE @existTableRulajeDoc bit
	
	SET @strRulajeDocAn = 'arhRulajeDoc'
	SET @existTableRulajeDoc = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeDocAn)
		SET @existTableRulajeDoc = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeDocAn+'](
	[IDDocument] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[NumarDoc] [decimal](18, 0) NOT NULL DEFAULT (0),
	[DataDoc] [datetime] NOT NULL DEFAULT (getdate()),
	[NumarNota] [varchar](50) NOT NULL DEFAULT (''''),
	[TipFormula] [smallint] NOT NULL DEFAULT (0),
	[IDJurnal] [int] NULL DEFAULT (0),
	[Observatii] [varchar](200) NULL,
	[xUser] [varchar](50) NOT NULL  DEFAULT (suser_sname()),
	[xHost] [varchar](50) NOT NULL DEFAULT (host_name()),
	[xDate] [datetime] NOT NULL  DEFAULT (0),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeDocAn+'] PRIMARY KEY CLUSTERED ([IDDocument] ASC),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strRulajeDocAn+'_Tip_FORMULA] CHECK  (([TipFormula] = 3 or ([TipFormula] = 2 or ([TipFormula] = 1 or [TipFormula] = 0))))
	)'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	---Rulaje--------------------
	
	DECLARE @strRulajeAn varchar(40)
	DECLARE @existTableRulaje bit
	
	SET @strRulajeAn = 'arhRulaje'
	SET @existTableRulaje = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeAn)
		SET @existTableRulaje = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeAn+'](
	[CodFormula] [bigint] NOT NULL,
	[ContDebit] [dbo].[TipCont] NOT NULL,
	[ContCredit] [dbo].[TipCont] NOT NULL,
	[CodValuta] [smallint] NOT NULL DEFAULT (0),
	[IDDocument] [bigint] NOT NULL,
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[Curs] [decimal](18, 4) NOT NULL DEFAULT (1),
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[DataDocJust] [datetime] NOT NULL,
	[TipDocJust] [varchar](10) NOT NULL  DEFAULT (''NC''),
	[NumarDocJust] [decimal](18, 0) NOT NULL,
	[ExplicatiiDocJust] [varchar](200) NOT NULL,
	[NrCrt] [smallint] NOT NULL DEFAULT (1),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeAn+'] PRIMARY KEY CLUSTERED ([CodFormula] ASC),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Credit] FOREIGN KEY([ContCredit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Debit] FOREIGN KEY([ContDebit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strRulajeDocAn+'] FOREIGN KEY([IDDocument]) REFERENCES ['+@strRulajeDocAn+'] ([IDDocument]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableRulaje = 0
		EXEC(@Comanda)
	

	---VenituriSiCheltuieli--------------------
	
	DECLARE @strVenituriSiCheltuieliAn varchar(40)
	DECLARE @existTableVenituriSiCheltuieli bit
	
	SET @strVenituriSiCheltuieliAn = 'arhVenituriSiCheltuieli'
	SET @existTableVenituriSiCheltuieli = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strVenituriSiCheltuieliAn)
		SET @existTableVenituriSiCheltuieli = 1

	SET @Comanda = 'CREATE TABLE ['+@strVenituriSiCheltuieliAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](20) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strVenituriSiCheltuieliAn+'] PRIMARY KEY CLUSTERED ([row] ASC,[CodUnitate] ASC,[An] ASC)
	)'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)



END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetConturiFolosite]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetConturiFolosite]
GO


CREATE PROCEDURE [dbo].[balcon_GetConturiFolosite]
	@CodUnitate smallint
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT co.* FROM Conturi co JOIN Rulaje rul ON (co.Cont=rul.ContDebit OR co.Cont=rul.ContCredit) AND co.CodUnitate=rul.CodUnitate
	WHERE co.CodUnitate =@CodUnitate
END


GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InchideAn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InchideAn]
GO

CREATE PROCEDURE [dbo].[InchideAn]  
@pAn as int,
@codUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhiva @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))

BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------
DELETE FROM [arhArticoleAliniate] WHERE [CodUnitate]=@codUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhArticoleAliniate] SELECT [ArtAl]
      ,[Denumire]
      ,[AAParinte]
      ,[Terminal]
      ,[Titlu]
      ,[Categorii]
      ,[TermDenumire]
      ,[StartDenumire]
      ,[Anexa6]
      ,[CodUnitate]
      ,@pAn
	  ,[ExecutieCheltuieli]
	   FROM [ArticoleAliniate] WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Bilant')
--Bilant---------------------------------------
DELETE FROM [arhBilant] WHERE [CodUnitate]=@codUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhBilant] SELECT *,@pAn FROM [Bilant] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Sterge rulaje')
DELETE FROM [arhRulaje] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE
	
PRINT('Sterge rulajeDoc')
DELETE FROM [arhRulajeDoc] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi')
--Conturi---------------------------------------
DELETE FROM [arhConturi] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],[An]) 
	SELECT [Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],@pAn FROM [Conturi] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
DELETE FROM [arhConturiValuta] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturiValuta] SELECT *,@pAn FROM [ConturiValuta] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('calculeaza rulajul total in credit precedent int abela conturi')
--calculeaza rulajul total in credit precedent int abela conturi

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 
	GOTO EROARE

--UPDATE [Conturi] SET 
--RulajDebit = DebitInitial + (SELECT coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 --year(rd.DataDoc)=@pAn and dbo.IsContInclus(Cont,r.ContDebit,Terminal)=1 AND r.[CodUnitate]=@CodUnit)
--WHERE cont<>'N/A' AND [CodUnitate]=@codUnit

UPDATE [Conturi] SET 
RulajDebit = a.RulajCurent FROM
(
 SELECT coalesce(SUM(r.ValoareLei),0) RulajCurent,r.ContDebit  FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 year(rd.DataDoc)=@pAn  AND r.[CodUnitate]=@CodUnit
 group by r.ContDebit
) a
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
and dbo.IsContInclus(Cont,a.ContDebit,Terminal)=1


IF @@ERROR != 0 
	GOTO EROARE

--UPDATE [Conturi] SET 
--RulajCredit = CreditInitial+(SELECT Coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE 
--year(rd.DataDoc)=@pAn and  dbo.IsContInclus(Cont ,r.ContCredit,Terminal)=1 AND r.[CodUnitate]=@codUnit)
--WHERE cont<>'N/A' AND [CodUnitate]=@codUnit

UPDATE [Conturi] SET 
RulajCredit = a.RulajCurent FROM
(
 SELECT coalesce(SUM(r.ValoareLei),0) RulajCurent,r.ContCredit  FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 year(rd.DataDoc)=@pAn  AND r.[CodUnitate]=@CodUnit
 group by r.ContCredit
) a
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
and dbo.IsContInclus(Cont,a.ContCredit,Terminal)=1



IF @@ERROR != 0 
GOTO EROARE

PRINT('calculeaza soldurile finale pe terminale')
--calculeaza soldurile finale pe terminale
UPDATE [Conturi] SET [DebitPrecedent] =[DebitInitial] - [CreditInitial] +  [RulajDebit] - [RulajCredit], [CreditPrecedent]=0 WHERE (Tip='A' OR (Tip='B' and ([DebitInitial] - [CreditInitial] +[RulajDebit] - [RulajCredit]>0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
UPDATE [Conturi] SET [CreditPrecedent] = [CreditInitial]  - [DebitInitial] + [RulajCredit] - [RulajDebit],[DebitPrecedent]=0 WHERE (Tip='P' OR (Tip='B' and ([DebitInitial] - [CreditInitial] + [RulajDebit] - [RulajCredit]<0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
--modifica soldurile initiale
UPDATE [Conturi] SET [DebitInitial] = [DebitPrecedent],[CreditInitial] = [CreditPrecedent] WHERE Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

-- nu trebuie sterse conturile in afara bilantului
--UPDATE [Conturi] SET [DebitInitial] = 0,[CreditInitial] = 0 WHERE Categorie<>0 AND [CodUnitate]=@CodUnit
--IF @@ERROR != 0 GOTO EROARE

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
DELETE FROM [arhJurnaleSolduriInitiale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhJurnaleSolduriInitiale] SELECT *,@pAn FROM [JurnaleSolduriInitiale]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Jurnale')
DELETE FROM [arhJurnale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhJurnale] SELECT *,@pAn FROM [Jurnale]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Conturi speciale')
DELETE FROM [arhConturiSpeciale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturiSpeciale] SELECT *,@pAn FROM [ConturiSpeciale] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Banci')
DELETE FROM [arhBanci] WHERE An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhBanci] SELECT *,@pAn FROM [Banci]
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Parteneri')
DELETE FROM [arhParteneri] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhParteneri] SELECT *,@pAn FROM [Parteneri]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Ordonantari')

DELETE FROM [arhOrdonantari]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 GOTO EROARE

INSERT INTO [arhOrdonantari] SELECT *,@pAn FROM [Ordonantari] WHERE YEAR(DataDoc)= @pAn AND [CodUnitate]=@CodUnit
IF @@ERROR != 0 GOTO EROARE

DELETE FROM [Ordonantari] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]= @CodUnit
IF @@ERROR != 0 GOTO EROARE


PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
DELETE FROM [arhRezultatPatrimonial] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhRezultatPatrimonial] SELECT *,@pAn FROM [RezultatPatrimonial] WHERE [CodUnitate]=@CodUnit
PRINT(@Comanda)
IF @@ERROR != 0 
	GOTO EROARE

PRINT('RulajeDoc')
--RulajeDoc---------------------------------------

INSERT INTO [arhRulajeDoc] SELECT 
	   [IDDocument]
      ,[ValoareLei]
      ,[ValoareValuta]
      ,[NumarDoc]
      ,[DataDoc]
      ,[NumarNota]
      ,[TipFormula]
      ,[IDJurnal]
      ,[Observatii]
      ,[xUser]
      ,[xHost]
      ,[xDate]
      ,[CodUnitate]
      ,@pAn
      ,[IDSursaFinantare]
      ,[IDSectorBugetar]
	  FROM [RulajeDoc] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Rulaje')
--Rulaje---------------------------------------

INSERT INTO [arhRulaje] SELECT a.[CodFormula]
      ,a.[ContDebit]
      ,a.[ContCredit]
      ,a.[CodValuta]
      ,a.[IDDocument]
      ,a.[ValoareValuta]
      ,a.[Curs]
      ,a.[ValoareLei]
      ,a.[DataDocJust]
      ,a.[TipDocJust]
      ,a.[NumarDocJust]
      ,a.[ExplicatiiDocJust]
      ,a.[NrCrt]
      ,a.[CodUnitate]
	  ,@pAn FROM [Rulaje] a JOIN [arhRulajeDoc] b ON a.IDDocument = b.IDDocument AND b.[CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


DELETE FROM [RulajeDoc] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]=@CodUnit 
IF @@ERROR != 0 
	GOTO EROARE

PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
DELETE FROM [arhVenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE	

INSERT INTO [arhVenituriSiCheltuieli] SELECT *,@pAn FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE	

IF EXISTS(SELECT * FROM [Arhive] WHERE [Anul]=@pAn AND [CodUnitate]=@codUnit)
BEGIN
	UPDATE [Arhive] SET [Balcon]=1 WHERE [CodUnitate]=@CodUnit AND [Anul]=@pAn
END
ELSE
BEGIN
	INSERT INTO [Arhive] ([Denumire],[CodUnitate],[Anul],[Balcon])  VALUES (@strAn, @CodUnit, @pAn,1)
END
IF @@ERROR != 0 
	GOTO EROARE	

UPDATE [BalConSetup] SET [AnCurent] = [AnCurent] + 1 WHERE [CodUnitate] =@CodUnit 
IF @@ERROR != 0 
	GOTO EROARE	

COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END


GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewRegistru]'))
DROP VIEW [dbo].[AmixViewRegistru]
GO


CREATE VIEW [dbo].[AmixViewRegistru]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
                      dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
                      dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, COALESCE (dbo.AmixAmortizare.ValoareInventar, dbo.AmixRegistru.ValoareInventar) 
                      AS ValoareInventar, COALESCE (dbo.AmixAmortizare.ValoareAmortizata, dbo.AmixRegistru.ValoareInventar) AS ValoareAmortizata, 
                      COALESCE (dbo.AmixAmortizare.ValoareRamasa, 0) AS ValoareRamasa, dbo.AmixAmortizare.AmortizareLuna, dbo.AmixAmortizare.DurataNormata, 
                      dbo.AmixAmortizare.LuniAmortizate, dbo.AmixTipStare.Denumire AS Stare, COALESCE (dbo.AmixAmortizare.Luna, 0) AS Luna, COALESCE (dbo.AmixAmortizare.An, 0) 
                      AS An, dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, COALESCE (dbo.AmixAmortizare.Blocat, 0) AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, 
                      dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie, dbo.AmixCatalog.Grupa, 
                      dbo.Departamente.IDDepartament, dbo.AmixRegistru.ContTransferForSuperior, dbo.AmixRegistru.ValoareIntrataTransfer, dbo.Jurnale.Codificare, 
                      dbo.AmixRegistru.ContTransfer
FROM         dbo.AmixRegistru LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.AmixRegistru.IDJurnal = dbo.Jurnale.IDJurnal LEFT OUTER JOIN
                      dbo.AmixAmortizare ON dbo.AmixAmortizare.IDMijlocFix = dbo.AmixRegistru.IDMijlocFix LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare LEFT OUTER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare LEFT OUTER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar

GO




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistru' AND COLUMN_NAME = 'IDSursaFinantare')
BEGIN
	ALTER TABLE dbo.AmixRegistru ADD IDSursaFinantare smallint NULL
	ALTER TABLE dbo.AmixRegistruBK ADD IDSursaFinantare smallint NULL

	ALTER TABLE dbo.AmixRegistru ADD CONSTRAINT
		FK_AmixRegistru_SurseFinantare FOREIGN KEY
		(IDSursaFinantare) REFERENCES dbo.SurseFinantare (IDSursaFinantare) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 

END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewRegistru]'))
DROP VIEW [dbo].[AmixViewRegistru]
GO


CREATE VIEW [dbo].[AmixViewRegistru]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, dbo.AmixRegistru.DataIesire, 
                      dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, dbo.AmixRegistru.DurataAmortizare, 
                      dbo.AmixRegistru.RegimAmortizare, COALESCE (dbo.AmixAmortizare.ValoareInventar, dbo.AmixRegistru.ValoareInventar) AS ValoareInventar, COALESCE (dbo.AmixAmortizare.ValoareAmortizata, 
                      dbo.AmixRegistru.ValoareInventar) AS ValoareAmortizata, COALESCE (dbo.AmixAmortizare.ValoareRamasa, 0) AS ValoareRamasa, dbo.AmixAmortizare.AmortizareLuna, 
                      dbo.AmixAmortizare.DurataNormata, dbo.AmixAmortizare.LuniAmortizate, dbo.AmixTipStare.Denumire AS Stare, COALESCE (dbo.AmixAmortizare.Luna, 0) AS Luna, 
                      COALESCE (dbo.AmixAmortizare.An, 0) AS An, dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, COALESCE (dbo.AmixAmortizare.Blocat, 0) AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, 
                      dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie, dbo.AmixCatalog.Grupa, dbo.Departamente.IDDepartament, 
                      dbo.AmixRegistru.ContTransferForSuperior, dbo.AmixRegistru.ValoareIntrataTransfer, dbo.Jurnale.Codificare, dbo.AmixRegistru.ContTransfer, dbo.AmixRegistru.IDSursaFinantare
FROM         dbo.AmixRegistru LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.AmixRegistru.IDJurnal = dbo.Jurnale.IDJurnal LEFT OUTER JOIN
                      dbo.AmixAmortizare ON dbo.AmixAmortizare.IDMijlocFix = dbo.AmixRegistru.IDMijlocFix LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare LEFT OUTER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare LEFT OUTER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_spViewRegistru]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_spViewRegistru]
GO


CREATE PROCEDURE [dbo].[amix_spViewRegistru]
	-- Add the parameters for the stored procedure here
	@filtru varchar(1000), 
	@an smallint,
	@luna smallint
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @cmd varchar(8000)
    SET @cmd =
	' SELECT  *  FROM (SELECT  ar.NrInventar, ar.Denumire, ar.ContAmortizare, ar.DataIntrare, ar.DataIesire, 
                      ar.CodUnitate,  ar.ContImobilizare, ar.ContCheltuiala, ar.DurataAmortizare, ar.IDMijlocFix,
                      ar.RegimAmortizare, ar.ValoareInventar, 
                      ar.Compus,ar.StocFaptic, ar.Categorie,ar.Blocat,ar.IDStare,
                      dbo.Departamente.Nume,
                      COALESCE (aa.ValoareAmortizata,CASE WHEN YEAR(ar.PunereInFunctiune)*100 + MONTH(ar.PunereInFunctiune) < '+CAST(@An*100+@luna AS varchar(6))+' THEN ar.ValoareInventar ELSE ar.ValoareAmortizata END) AS ValoareAmortizata, 
                      COALESCE (aa.ValoareRamasa, 0) AS ValoareRamasa, COALESCE (aa.AmortizareLuna, 0) AS AmortizareLuna, COALESCE (aa.DurataNormata, 0) 
                      AS DurataNormata, aa.LuniAmortizate, COALESCE (aa.Luna, 0) AS Luna, COALESCE (aa.An, 0) AS An, 
                      COALESCE (aa.Blocat, 0) AS AmortizareBlocata,
                      dbo.AmixTipStare.Denumire AS Stare, 
                      dbo.AmixGestionari.Nume AS Gestionar, 
                      dbo.AmixCatalog.Cod,dbo.AmixCatalog.Grupa,ContTransferForSuperior, ValoareIntrataTransfer,j.Codificare,ar.ContTransfer,ar.IDSursaFinantare
		FROM  dbo.AmixRegistru ar 
					  LEFT OUTER JOIN 
					  (SELECT * FROM AmixAmortizare aa WHERE aa.Luna='+CAST(@Luna AS varchar(2))+' and aa.An= ' + cast(@An as varchar(4)) + ') aa  ON ar.IDMijlocFix = aa.IDMijlocFix 
                      LEFT OUTER JOIN  dbo.AmixCatalog ON ar.IDClasificare = dbo.AmixCatalog.IDClasificare 
                      LEFT OUTER JOIN dbo.Departamente ON ar.IDDepartament = dbo.Departamente.IDDepartament 
                      LEFT OUTER JOIN dbo.AmixTipStare ON ar.IDStare = dbo.AmixTipStare.IDStare 
					  LEFT OUTER JOIN dbo.Jurnale j ON ar.IDJurnal = j.IDJurnal 
                      LEFT OUTER JOIN dbo.AmixGestionari ON ar.IDGestionar = dbo.AmixGestionari.IDGestionar) fs '
     IF @filtru <> ''
		SET @cmd = @cmd + ' WHERE ' + @filtru
     EXEC(@cmd) 
 
END


GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixDocumente' AND COLUMN_NAME = 'IDSursaFinantare')
BEGIN
	ALTER TABLE dbo.AmixDocumente ADD IDSursaFinantare smallint NULL


	ALTER TABLE dbo.AmixDocumente ADD CONSTRAINT
		FK_AmixDocumente_SurseFinantare FOREIGN KEY
		(IDSursaFinantare) REFERENCES dbo.SurseFinantare (IDSursaFinantare) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_InchideLuna2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_InchideLuna2]
GO


CREATE PROCEDURE [dbo].[amix_InchideLuna2] 
	-- Add the parameters for the stored procedure here
	@Luna smallint, 
	@An smallint,
	@CodUnitate smallint,
	@IDDocument int, --daca document e zero pe toate
	@InchideLuna bit --daca se inchide luna sau nu
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
	-- numarul mijloacelor fixe cu acelasi numar de inventar
	DECLARE @MaxDurata int
	DECLARE @Index int
	DECLARE @Compus int
	DECLARE @UltimaZiLuna datetime
	DECLARE @LunaCurenta smallint 
	DECLARE @AnCurent smallint

	SELECT @LunaCurenta =LunaCurenta, @AnCurent=AnulCurent  FROM AmixSetup WHERE CodUnitate=@CodUnitate

	SET @UltimaZiLuna = DATEADD(day,-1,DATEADD(month,1,CAST(CAST(@An AS varchar(4)) + '-' + CAST(@Luna AS varchar(2)) + '-01' AS datetime)))
 
 
	IF @IDDocument=0
		SET @Compus = 1
	ELSE
		--daca documentul a fost sters le actualizeaza pe toate!
		SELECT @Compus = ISNULL([Compus],1) FROM [AmixDocumente] WHERE  [IDDocument]=@IDDocument	
	PRINT('Compus=' + cast(@Compus as varchar(2)))
	--sterge mijloacele fixe introduse in luna curenta
	BEGIN TRAN
	
	---sterge din tabela de amortizari inregistrarile generate
	-- daca suntem dupa luna curenta
	IF @AnCurent*12 + @LunaCurenta <= @An*12+@Luna
	BEGIN
		DELETE FROM [AmixAmortizare] WHERE [IDMijlocFix] IN (SELECT [IDMijlocFix] FROM [AmixRegistru]
			WHERE [CodUnitate]=@CodUnitate) AND [An] = @An AND [Luna]=@Luna
		IF @@ERROR <>0 GOTO Eroare  
	
		DELETE FROM [AmixRegistruBK] WHERE  [CodUnitate]=@CodUnitate AND [An] = @An AND [Luna]=@Luna
		IF @@ERROR <>0 GOTO Eroare  	

		---------------------------------------------------------------------------------------------------
		----------------------------------------Backup-----------------------------------------------------
		--- se salveaza toate mijloacele fixe
		INSERT INTO [AmixRegistruBK]
			   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare]
				,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]
				,[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI]
				,[Categorie],[IDUnitateMasura],Luna,An,[ContTransferForSuperior],[IDJurnal],[ValoareIntrataTransfer],[IDSursaFinantare])
				SELECT [IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare]
				,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]
				,[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI]
				,[Categorie],[IDUnitateMasura],@LunaCurenta,@AnCurent,[ContTransferForSuperior],[IDJurnal],[ValoareIntrataTransfer],[IDSursaFinantare]
			   FROM [AmixRegistru] a 
			WHERE  [CodUnitate]=@CodUnitate 
		IF @@ERROR <>0 GOTO Eroare  	

		UPDATE [AmixDocumente] SET [IDBK] = b.[IDBK] FROM [AmixRegistruBK] b 
		WHERE [AmixDocumente].[NrInventar] = b.[NrInventar] AND [AmixDocumente].[CodUnitate] = b.[CodUnitate]
		IF @@ERROR <>0 GOTO Eroare  	

		----------------------------------------Backup-----------------------------------------------------
	
	END
	
	---------------------------------------------------------------------------------------------------

	--nu se pot sterge inregistrarile din registru pentru ca sunt legate de amortizare
	-- ele se updateaza mijloacele fixe existente
	-------------------------------------------------------------------------------
	UPDATE [AmixRegistru] 
			SET [NrInventar] = d.[NrInventar],
			[Denumire]=d.[Denumire],
			[ContAmortizare]=d.[ContAmortizare],
			[StocFaptic]=1,
			[IDClasificare]=d.[IDClasificare],
            [DataIntrare]=CASE WHEN d.Compus<3 THEN DATEADD(month,1,d.[PunereInFunctiune]) ELSE d.[DataDoc] END,
            [DataIesire]=DATEADD(month,d.[DurataAmortizare]-d.[LuniAmortizate],d.[PunereInFunctiune]),
			[CodUnitate]=@CodUnitate,
			[IDDepartament]=d.[IDDepartament],
			[IDStare]=5,
			[ContImobilizare]=d.[ContImobilizare],
			[ContCheltuiala]=d.[ContCheltuiala],
			[DurataAmortizare]=d.[DurataAmortizare],
			[RegimAmortizare]=d.[RegimAmortizare],
			[ValoareInventar]=d.[ValoareInventar],
			[PunereInFunctiune]=d.[PunereInFunctiune],[Compus]=d.[Compus],
			[IDGestionar]=d.[IDGestionar],
			[AmortizareLuna] = d.[AmortizareLuna],
			[AmortizareAn] = d.[AmortizareAn], -- de fapt este vorba de amortizarea in primul an
			[ValoareAmortizata] = d.ValoareAmortizata,
			[IDUnitateMasura] = d.[IDUnitateMasura],
			[CantitateOI] = d.CantitateOI,
			[InFolosinta] = d.[InFolosinta],
			[ContTransferForSuperior] = d.[ContTransferForSuperior],
			[IDSursaFinantare]=d.[IDSursaFinantare]
			FROM [AmixDocumente] d
	WHERE  
		d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=2 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  	
	--pentru modernizare
	PRINT('actualizarile pentru modernizare in registru')
    --atentie se modifica si mortizarea lunara si Amorizarea anuala
	UPDATE [AmixRegistru] 
			SET [DurataAmortizare]=d.[DurataAmortizare]+ d.[DurExtindere],[ValoareInventar]=d.[ValoareInventar] + d.[ValoareSuplimentara],
            [AmortizareLuna] = CASE WHEN [AmixRegistru].[RegimAmortizare]= 1 THEN d.[AmortizareLuna]
				WHEN [AmixRegistru].[RegimAmortizare]= 2 THEN d.[AmortizareLuna]
				WHEN [AmixRegistru].[RegimAmortizare]= 3 THEN  -- accelerata
					CASE WHEN DATEDIFF(month,[AmixRegistru].[PunereInFunctiune],@UltimaZiLuna)<12 THEN d.[AmortizareLuna]
						 WHEN DATEDIFF(month,[AmixRegistru].[PunereInFunctiune],@UltimaZiLuna)=12 THEN d.[AmortizareAn] - d.[AmortizareLuna]*11
						ELSE (d.[ValoareInventar] - d.[ValoareAmortizata] - d.[AmortizareAn])/(d.[DurataAmortizare]-12)
					END 
				ELSE 0
				END
			FROM [AmixDocumente] d
	WHERE  
		d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=8 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  

	--se adauga mfixe noi
	------------------------------------------------------------------------------
	PRINT('Adauga noi MF')
	INSERT INTO [AmixRegistru]
           ([NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare]
           ,[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare],[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
			[PunereInFunctiune],[Compus],[IDGestionar],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[CantitateOI],[IDUnitateMasura],[InFolosinta])
	(SELECT [NrInventar],[Denumire],[ContAmortizare],1,[IDClasificare],
	 CASE WHEN Compus<3 THEN DATEADD(month,1,[PunereInFunctiune]) ELSE [PunereInFunctiune] END,
	 DATEADD(month,[DurataAmortizare],[PunereInFunctiune]),
	 @CodUnitate,[IDDepartament],5,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
	 [PunereInFunctiune],[Compus],[IDGestionar],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[CantitateOI],[IDUnitateMasura],[InFolosinta]
	 FROM [AmixDocumente] ad
	 WHERE [CodUnitate]=@CodUnitate AND [IDTipDocument]=2 AND [Anul]=@An AND [Luna]=@Luna
		AND ad.[IDMijlocFix] IS NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
		AND NOT EXISTS(SELECT * FROM AmixRegistru ar WHERE ar.CodUnitate=ad.CodUnitate AND ar.NrInventar=ad.NrInventar))
		
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------------------
	PRINT('Face legatura cu registrul pentru toate documentele')

	UPDATE [AmixDocumente] SET [IDMijlocFix] = a.[IDMijlocFix] FROM [AmixRegistru] a
		WHERE a.[NrInventar]=[AmixDocumente].[NrInventar] AND
			  [AmixDocumente].[CodUnitate]=@CodUnitate AND [AmixDocumente].[Anul]=@An AND [AmixDocumente].[Luna]=@Luna AND [AmixDocumente].[IDMijlocFix] IS NULL
			  AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	
	IF @@ERROR <>0 GOTO Eroare  

	--Procesare Mijloace fixe compuse
	-------------------------------------------------------------------------
	IF @Compus>=1
	BEGIN
		Print('Prelucrare mijloace fixe compuse')
		UPDATE [AmixRegistru]
		SET [DataIntrare]= a2.[DataIntrare],
			[DataIesire] = a2.[DataIesire],
			[ValoareInventar] = a2.[ValoareInventar],
			[DurataAmortizare] = a2.[DurataAmortizare],
			[PunereInFunctiune] = a2.[PunereInFunctiune]
		FROM (SELECT b.[IDMFixPrincipal] AS [IDMijlocFix], 
				MIN([PunereInFunctiune]) AS [PunereInFunctiune],
				DATEDIFF(month,MIN([DataIntrare]),MAX([DataIesire]))+1 AS [DurataAmortizare],			
				SUM([ValoareInventar]) AS [ValoareInventar],
				MAX([DataIesire]) AS [DataIesire],
				MIN([DataIntrare]) AS [DataIntrare]
				FROM [AmixRegistru] a JOIN [AmixAnsamble] b ON a.[IDMijlocFix] = b.[IDMfixComponenta] 
				WHERE a.[Compus] = 2
				GROUP BY b.[IDMFixPrincipal]) a2
		WHERE 
		[Compus] = 1 AND 
		[CodUnitate]=@CodUnitate AND 
		[AmixRegistru].[IDMijlocFix] = a2.[IDMijlocFix]
		
		IF @@ERROR <>0 GOTO Eroare
	END

	Print('Conservare mijloc fix')
	----Conservare mijloc fix----------------------------------------
	UPDATE [AmixRegistru] 
			SET [IDStare] = 6
			FROM [AmixDocumente] d
	WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=5 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------

	----Reactivare mijloc fix-------------------------------------------

	DECLARE @recalcul int
	SET @recalcul=0	
	Print('Reactivare mijloc fix')

	UPDATE [AmixRegistru] 
			SET [IDStare] = 5,
			[PunereInFunctiune] = d.[PunereInFunctiune]
			FROM [AmixDocumente] d
	WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=6 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------
	IF @AnCurent*12 + @LunaCurenta <= @An*12+@Luna
	BEGIN
		PRINT('Calcul Amortizare lunara si pentru ultima luna')
		-- generare amortizare  luna curenta pentru mijloace fixe obisnuite
		INSERT INTO [AmixAmortizare]
		   ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa]
		   ,[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate])	
		SELECT [IDMijlocFix],
		@Luna,@An,ar.[ValoareInventar],ar.ValoareAmortizata,ar.[ValoareInventar] - ar.ValoareAmortizata,ar.[AmortizareAn],
		ar.[AmortizareLuna],[DurataAmortizare],
		CASE WHEN (DATEDIFF(month,[PunereInFunctiune],@UltimaZiLuna)-1) < 0 THEN 0
		ELSE (DATEDIFF(month,[PunereInFunctiune],@UltimaZiLuna)-1) END -- numarul de luni amortizare
		FROM [AmixRegistru] ar
		WHERE [CodUnitate]=@CodUnitate AND [RegimAmortizare]<>0 AND [Compus]<>1
		AND ar.[IDMijlocFix] NOT IN (SELECT [IDMijlocFix] FROM [AmixDocumente] d
			WHERE  d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument))
		AND ar.[IDStare]=5 AND (YEAR([PunereInFunctiune])*100+MONTH([PunereInFunctiune]) < YEAR(@UltimaZiLuna)*100+MONTH(@UltimaZiLuna))
		IF @@ERROR <>0 GOTO Eroare  	


		--generare amortizare mijloace compuse
		-----------------------------------------------------------------------------------
		
		IF @Compus >= 1
		BEGIN
			Print('Prelucrare amortizare mijloace fixe compuse')
			
			INSERT INTO [AmixAmortizare]
			   ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa]
			   ,[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate])
			(SELECT b.[IDMFixPrincipal],
				@Luna,@An,
				r1.[ValoareInventar],
				SUM(ISNULL(a.[ValoareAmortizata],0)), 
				SUM(ISNULL(a.[ValoareRamasa],r2.[ValoareInventar])),
				SUM(ISNULL(a.[AmortizareAn],0)),
				SUM(ISNULL(a.[AmortizareLuna],0)),
				r1.[DurataAmortizare],
				CASE WHEN DATEDIFF(month,r1.[PunereInFunctiune],@UltimaZiLuna)-1 < 0 THEN 0
					 ELSE DATEDIFF(month,r1.[PunereInFunctiune],@UltimaZiLuna)-1 END
			FROM [AmixAnsamble] b
				LEFT OUTER JOIN [AmixAmortizare] a   ON a.[IDMijlocFix] = b.[IDMfixComponenta] AND a.[Luna] = @Luna AND a.[An]=@An
				JOIN [AmixRegistru] r2 ON b.[IDMfixComponenta]  = r2.[IDMijlocFix] 
				JOIN [AmixRegistru] r1 ON b.[IDMFixPrincipal] = r1.[IDMijlocFix]
			WHERE 
				(YEAR(r1.[PunereInFunctiune])*12 + MONTH(r1.[PunereInFunctiune])+1 <= @An*12+@luna) AND --din luna urmatoare datei de punere in functiune
				(@An*12+@Luna <= YEAR(r1.[PunereInFunctiune])*12+MONTH(r1.[PunereInFunctiune]) + r1.[DurataAmortizare])
				AND r1.[CodUnitate]=@CodUnitate  
				AND r2.[IDStare]=5
		   GROUP BY b.[IDMFixPrincipal],r1.[ValoareInventar],r1.[DurataAmortizare],r1.[PunereInFunctiune])

			IF @@ERROR <>0 GOTO Eroare  	

		END	
	END
	
	Print('actualizeaza valorile pentru documentele compuse')
	--actualizeaza valorile pentru documentele compuse
	UPDATE [AmixDocumente] SET [ValoareInventar] = a.[ValoareInventar],
	[ValoareAmortizata] = a.[ValoareAmortizata]
	FROM [AmixRegistru] a WHERE [AmixDocumente].[CodUnitate]=@CodUnitate
	AND [AmixDocumente].[IDMijlocFix]=a.[IDMijlocFix] AND [AmixDocumente].[Luna]=@Luna AND [AmixDocumente].[Anul]=@An
	AND [AmixDocumente].[Compus]=1
	IF @@ERROR <>0 GOTO Eroare

	

	----Blocheaza amortizarea mijloacelor fixe care pleaca din gestiune
	----trece mijloacele in mijloace iesite
	Print('trece mijloacele in mijloace iesite')
	UPDATE [AmixRegistru] 
			SET [IDStare] = 2,[InFolosinta]=0
			FROM [AmixDocumente] d
		WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	-- Blocheaza amortizarile pentru mijloacele fixe care pleaca din gestiune
	Print('Blocheaza amortizarile pentru mijloacele fixe care pleaca din gestiune')
	UPDATE [AmixAmortizare] 
				SET [Blocat] = 1
				FROM [AmixDocumente] d
			WHERE  
				d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 AND d.[IDMijlocFix] = [AmixAmortizare].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
				AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
				AND [AmixAmortizare].[An]*100 + [AmixAmortizare].[Luna] >= @An*100+@Luna
	IF @@ERROR <>0 GOTO Eroare  

	Print('Transfer intern, schimbare gestionar')
	----Transfer intern, schimbare gestionar
	UPDATE [AmixRegistru] 
			SET [IDDepartament] = d.[IDDepartament],
			[IDgestionar] = d.[IDGestionar]
			FROM [AmixDocumente] d
		WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=4 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  




	Print('Inchidere Luna')
	----------- inchidere luna
	IF @InchideLuna=1
	BEGIN
		DECLARE @azi DateTime
		SET @azi = CAST(CAST(@An as varchar(4)) + '-' + cast(@Luna as varchar(2)) + '-01' AS DateTime)
		UPDATE [AmixSetup] SET [LunaCurenta]= MONTH(DATEADD(month,1,@azi)),[AnulCurent]=YEAR(DATEADD(month,1,@azi))
		WHERE [CodUnitate]=@CodUnitate
		IF @@ERROR <>0 GOTO Eroare 
		SET @Luna = MONTH(DATEADD(month,1,@azi))
		SET @An = YEAR(DATEADD(month,1,@azi))


		Print('Schimba stare mijloace amortizate')
		UPDATE [AmixRegistru] SET [AmortizareLuna] = 0,[AmortizareAn]=0,[IDStare]=1
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate  AND [ValoareAmortizata] + AmortizareLuna >= [ValoareInventar] AND  [ValoareAmortizata] >= [ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


		Print('actualizeaza in registru valoarea de inventar si valoare amortizata in ultima luna')
		UPDATE [AmixRegistru] SET [AmortizareLuna] = [ValoareInventar] - [ValoareAmortizata],
		[ValoareAmortizata] = [ValoareInventar],[LuniAmortizate] = DurataAmortizare
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate  AND [ValoareAmortizata] + AmortizareLuna >= [ValoareInventar] AND  [ValoareAmortizata] < [ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


		Print('actualizeaza in registru valoarea de inventar si valoare amortizata')
		--actualizeaza in registru valoarea de inventar si valoare amortizata
		UPDATE [AmixRegistru] SET 
		[ValoareAmortizata] = [ValoareAmortizata]+AmortizareLuna,[LuniAmortizate] = [LuniAmortizate]+1
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate AND [ValoareAmortizata]+AmortizareLuna <[ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


	
		EXEC [amix_InchideLuna2] @Luna,@An,@CodUnitate,0,0;
	END 
	COMMIT

	---inchidere luna

	GOTO Gata
	DUBLURI:
		RAISERROR ('Exista mijloace fixe cu acelasi numar de inventar',16,-1)
		GOTO Gata

	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la prelucrarea datelor',16,-1)
		GOTO Gata

		GATA:
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_DeschideLuna]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_DeschideLuna]
GO


CREATE PROCEDURE [dbo].[amix_DeschideLuna] 
	-- Add the parameters for the stored procedure here
	@Luna smallint, 
	@An smallint,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
  
	--sterge mijloacele fixe introduse in luna curenta
	BEGIN TRAN		
		
		UPDATE [AmixRegistru]
		SET    [NrInventar] = arh.NrInventar 
			  ,[Denumire] = arh.Denumire
			  ,[ContAmortizare] = arh.ContAmortizare
			  ,[StocFaptic] = arh.StocFaptic
			  ,[IDClasificare] = arh.IDClasificare
			  ,[DataIntrare] = arh.DataIntrare
			  ,[DataIesire] = arh.DataIesire
			  ,[IDDepartament] = arh.IDDepartament
			  ,[IDStare] = arh.IDStare
			  ,[ContImobilizare] = arh.ContImobilizare
			  ,[ContCheltuiala] = arh.ContCheltuiala
			  ,[DurataAmortizare] = arh.DurataAmortizare
			  ,[RegimAmortizare] = arh.RegimAmortizare
			  ,[ValoareInventar] = arh.ValoareInventar
			  ,[PunereInFunctiune] = arh.PunereInFunctiune
			  ,[Compus] = arh.Compus
			  ,[Blocat] = arh.Blocat
			  ,[IDGestionar] = arh.IDGestionar
			  ,[ContTransfer] = arh.ContTransfer
			  ,[ValoareAmortizata] = arh.ValoareAmortizata
			  ,[LuniAmortizate] = arh.LuniAmortizate
			  ,[AmortizareLuna] = arh.AmortizareLuna
			  ,[AmortizareAn] = arh.AmortizareAn
			  ,[InFolosinta] = arh.InFolosinta
			  ,[CantitateOI] = arh.CantitateOI
			  ,[Categorie] = arh.Categorie
			  ,[IDUnitateMasura] = arh.IDUnitateMasura
			  ,[ContTransferForSuperior] = arh.ContTransferForSuperior
			  ,[IDJurnal]=arh.IDJurnal
			  ,[ValoareIntrataTransfer]=arh.ValoareIntrataTransfer
			  ,IDSursaFinantare = arh.IDSursaFinantare
			   FROM [AmixRegistruBK] arh  
			WHERE  [AmixRegistru].IDMijlocFix = arh.IDMijlocFix AND arh.Luna = @Luna AND arh.An=@An
		
		IF @@ERROR <>0 GOTO Eroare

		UPDATE [AmixSetup] SET [LunaCurenta]= @Luna,[AnulCurent]=@An
		WHERE [CodUnitate]=@CodUnitate
		IF @@ERROR <>0 GOTO Eroare 
		
		
	    EXEC [amix_InchideLuna2] @Luna,@An,@CodUnitate,0,0;

	COMMIT

	---inchidere luna

	GOTO Gata
	DUBLURI:
		RAISERROR ('Exista mijloace fixe cu acelasi numar de inventar',16,-1)
		GOTO Gata

	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la prelucrarea datelor',16,-1)
		GOTO Gata

		GATA:
END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSursaFinantare]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetSursaFinantare]
GO


CREATE FUNCTION [dbo].[GetSursaFinantare] (@Cont TipCont)  
RETURNS varchar(1) AS  
BEGIN 
DECLARE @ret varchar(1)
SET @ret=''
IF LEN(@Cont) >= 11
	SET @ret = SUBSTRING(LTRIM(RTRIM(@Cont)),10,1)
return @ret
END

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalanta2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalanta2014]
GO




CREATE PROCEDURE [dbo].[balcon_GetBalanta2014] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@filtJurnal varchar(1000),
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@SurseFinantare varchar(500)	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (D.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON  dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (LEN(LTRIM(RTRIM(@filtJurnal)))=0 OR (rjd.IDJurnal IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtJurnal,',') fart)))
			) --exists	 
			OR (LEN(LTRIM(RTRIM(@filtJurnal)))=0  AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	AND (LEN(@surseFinantare)=0 OR (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters(@SurseFinantare,',') fart)))	
	ORDER BY Cont 
					

END

GO






IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenListaBalanta2014]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenListaBalanta2014]
GO



CREATE PROCEDURE [dbo].[balcon_GenListaBalanta2014]
	@An smallint   
	,@CodUnitate smallint
	,@categorie tinyint
	,@analitica bit
	,@cfm bit
	,@filtruJurnale varchar(500)
	,@DataEnd datetime
	,@ptgc bit
	,@ContStart as TipCont
	,@ContEnd as TipCont
	,@Extinsa bit
	,@filtruSurseFinantare varchar(500)
AS

DECLARE @DataStart as datetime
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @strcategorie as varchar(10)
DECLARE @dataStartFunctie varchar(50)
DECLARE @laData varchar(50)
DECLARE @ultimaData datetime


SET @DataStart = DATEADD(Day, 1-DAY(@DataEnd),@DataEnd)




DECLARE	@return_value int
PRINT('DECLARE @ContStart as TipCont,@ContEnd as TipCont
DECLARE @DataStart as datetime, @DataEnd as datetime
DECLARE @categorie as tinyint
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @jurnal varchar(1000)
DECLARE @pAn smallint
DECLARE @ptgc bit
DECLARE @CodUnitate smallint
DECLARE	@return_value int
')

PRINT('SET @DataStart = ''' + cast(@DataStart as varchar(20)) + '''')
PRINT('SET @DataEnd=''' + cast(@DataEnd as varchar(100)) + '''')
PRINT('SET @jurnal=''' + @filtruJurnale +'''')
PRINT('SET @categorie=' + cast(@categorie as varchar(100)))
PRINT('SET @ContStart=''' + cast(@ContStart as varchar(100)) +'''')
PRINT('SET @ContEnd =''' + cast(@ContEnd as varchar(100)) + '''')
PRINT('SET @cfm=' + cast(@cfm as varchar(100)))
PRINT('SET @analitica=' + cast(@analitica as varchar(100)))
PRINT('SET @pAn=' + cast(@An as varchar(100)))
PRINT('SET @ptgc=' + cast(@ptgc as varchar(100)))
PRINT('SET @CodUnitate=' + cast(@CodUnitate as varchar(100)))

IF @An=0
BEGIN
	IF @ptgc = 1
		EXEC	@return_value = [dbo].[balcon_GetBalantaTGC2014]
		@CodUnitate = @CodUnitate,
		@pDtDataStartUI = @DataStart,
		@pDtDataEndUI = @DataEnd,
		@filtJurnal = @filtruJurnale,
		@pCategorie = @categorie,
		@pStrContStart = @ContStart,
		@pStrContEnd = @ContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0
	ELSE
		IF @Extinsa=1
			EXEC	@return_value = [dbo].[balcon_GetBalantaExtinsa2014]
			@CodUnitate = @CodUnitate,
			@pDtDataStartUI = @DataStart,
			@pDtDataEndUI = @DataEnd,
			@filtJurnal = @filtruJurnale,
			@pCategorie = @categorie,
			@pStrContStart = @ContStart,
			@pStrContEnd = @ContEnd,
			@pCfm = @cfm,
			@pAnalitica = @analitica,
			@pCfmRulaje = 1,
			@pTerminal = 0
		ELSE
			EXEC	@return_value = [dbo].[balcon_GetBalanta2014]
			@CodUnitate = @CodUnitate,
			@pDtDataStartUI = @DataStart,
			@pDtDataEndUI = @DataEnd,
			@filtJurnal = @filtruJurnale,
			@pCategorie = @categorie,
			@pStrContStart = @ContStart,
			@pStrContEnd = @ContEnd,
			@pCfm = @cfm,
			@pAnalitica = @analitica,
			@pCfmRulaje = 1,
			@pTerminal = 0,
			@SurseFinantare = @filtruSurseFinantare
END
ELSE
		EXEC	@return_value = [dbo].[balcon_GetBalantaArh2014]
		@CodUnitate = @CodUnitate,
		@pDtDataStartUI = @DataStart,
		@pDtDataEndUI = @DataEnd,
		@filtJurnal = @filtruJurnale,
		@pCategorie = @categorie,
		@pStrContStart = @ContStart,
		@pStrContEnd = @ContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@pAn = @An


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata:

GO


UPDATE [AmixRegistru]  SET [Categorie]='ALTELE' where (Categorie='') or (Categorie is null)
GO




DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [ParamListe] WHERE [IDLista]=11 AND [COD]='SFI'
IF @Cate= 0 
BEGIN
INSERT INTO [ParamListe]
           ([IDLista]
           ,[Cod]
           ,[NrCrt]
           ,[Denumire]
           ,[Valoare]
           ,[ValoareImplicita]
           ,[Tip]
           ,[Validare]
           ,[SelRezumat]
           ,[User]
           ,[Host]
           ,[CodUnitate])
     VALUES
           (11
           ,'SFI'
           ,7
           ,'Sursa finantare'
           ,''
           ,''
           ,'R'
           ,''
           ,'SELECT'
           ,'0'
           ,'0'
           ,0)
END
GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [ParamListe] WHERE [IDLista]=13 AND [COD]='SFI'
IF @Cate= 0 
BEGIN
INSERT INTO [ParamListe]
           ([IDLista]
           ,[Cod]
           ,[NrCrt]
           ,[Denumire]
           ,[Valoare]
           ,[ValoareImplicita]
           ,[Tip]
           ,[Validare]
           ,[SelRezumat]
           ,[User]
           ,[Host]
           ,[CodUnitate])
     VALUES
           (13
           ,'SFI'
           ,8
           ,'Sursa finantare'
           ,''
           ,''
           ,'R'
           ,''
           ,'SELECT'
           ,'0'
           ,'0'
           ,0)
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenListaExecutieChelt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenListaExecutieChelt]
GO



CREATE PROCEDURE [dbo].[GenListaExecutieChelt]   
@idLista smallint, @CodUnitate smallint, 
@An varchar(4)
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @strCapitol as varchar(100)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @tipb as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @fni bit
DECLARE @centr bit
DECLARE @idJurn smallint
DECLARE @strFiltruCapitol varchar(200)
DECLARE @strFiltruSS varchar(200)

SET @idJurn = -1
SET @categorie = 0
SET @strcategorie = '0'
SET @analitica = 0
SET @cfm = 0
SET @fni = 0
SET @centr = 0

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SET @strDataStart = LTRIM(@strDataStart)
SET @strDataStart = RTRIM(@strDataStart)
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float)-2  AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)

IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END

SELECT @strContStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SELECT @strContEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate



--conturi fara miscari
SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @cfm =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @fni =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CNT' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @centr =1

SELECT @strCapitol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SELECT @idJurn = IDJurnal FROM [Jurnale] WHERE [Codificare]=@strCapitol  AND [CodUnitate]=@CodUnitate


	
SET @strFiltruCapitol = ''
if @idJurn>0 AND @idJurn is NOT NULL
	SET @strFiltruCapitol = ' AND IDJurnal='+cast(@idJurn as varchar(10))

SET @strFiltruSS = ''
SELECT @strFiltruSS=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='SFI' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate

if LEN(RTRIM(LTRIM(@strFiltruSS)))>0 AND @idJurn is NOT NULL
	SET @strFiltruSS = ' AND (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters('''+@strFiltruSS+''','','') fart))'	



IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @ca=@ca + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @ab=@ab + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate
SELECT @al=@al + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate 
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@ca+@ab+@al


CREATE TABLE [#balanta_t] (
	[ArtAl] varchar(50) NOT NULL, 
	[AAParinte] varchar(50) NOT NULL, 
	[Categorii] varchar(50) NOT NULL, 
	[Denumire] varchar(400) NOT NULL DEFAULT '',
	[Anexa6] bit NOT NULL DEFAULT 0,	
	[Terminal] bit NOT NULL,
	[Titlu] varchar(100) NOT NULL,

	[CrediteAprobate] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrediteDeschise] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteBugetare] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteLegale] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[PlatiNete] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DisponibilCredit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[Cheltuieli] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[DebitInitial] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreditInitial] DECIMAL(20,2) NOT NULL DEFAULT 0
)

DECLARE @strSelect	varchar(8000)
DECLARE @filtData varchar(500)
DECLARE @filtDataC varchar(500)
DECLARE @filtC varchar(500)
DECLARE @filtCC varchar(500)
DECLARE @strCodUnitate varchar(50)
DECLARE @arh varchar(4)
DECLARE @filtruArh varchar(300)
DECLARE @filtruArhRulajeDoc varchar(300)


SET @filtData = ''
SET @filtDataC = ''
SET @filtC = ''
SET @filtCC = ''
SET @strCodUnitate = CAST(@CodUnitate AS varchar(50))

SET @arh = ''
SET @filtruArh=''
SET @filtruArhRulajeDoc=''
IF @An<>''
BEGIN
	SET @arh='arh'
	SET @filtruArh=' AND An=' + @An
	SET @filtruArhRulajeDoc=' AND D.An=' + @An
END	

IF @idLista = 1
	BEGIN
		IF LEN(@strDataEnd) <>0
			SET @filtData =  ' AND D.DataDoc <='+@strDataEnd		
	END
ELSE
	BEGIN
		IF LEN(@strDataStart) <>0
			SET @filtData =  ' AND D.DataDoc >='+@strDataStart
		IF LEN(@strDataEnd) <>0
			SET @filtData = @filtData + '  AND D.DataDoc <='+@strDataEnd	
	END
IF LEN(@strContStart) <>0
	SET @filtC =  '  AND F.ContDebit >='''+@strContStart + ''' '
IF LEN(@strContEnd) <>0
	SET @filtC = @filtC + ' AND F.ContDebit <='''+@strContEnd + ''' '

SET @filtData = @filtData + @filtC
IF @fni = 1
	SET @filtData =@filtData + '  AND F.TipDocJust <>''IC'' '  

SET @filtDataC = @filtData
SET @filtDataC = REPLACE(@filtDataC,'F.ContDebit','F.ContCredit') 
PRINT 'Total' + @filtDataC
SET @filtCC = REPLACE(@filtC,'F.ContDebit','Cont') 
SET @filtC = REPLACE(@filtC,'F.ContDebit','a.ArtAl') 

--	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
--	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
--	Cnt.[DebitInitial],Cnt.[CreditInitial]

SET @strSelect =' INSERT INTO [#balanta_t]
	 SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu,
	SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
	SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
	SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
	SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
	SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
	SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
	SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
FROM [ArticoleAliniate] a LEFT OUTER JOIN 
(SELECT 
dbo.GetAnalitic(Cont) Cont,[CodUnitate],
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',DebitInitial)) DebitInitial,
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',CreditInitial)) CreditInitial
FROM
['+@arh+'Conturi]
WHERE Terminal = 1 AND Cont<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(Cont)+'','','''+@toate+''')<>0 ' + @filtCC + '
 AND [CodUnitate] = '+@strCodUnitate+@filtruArh +
 @strFiltruSS + 
 'GROUP BY dbo.GetAnalitic(Cont),[CodUnitate]) CNT
ON dbo.IsContInclus(a.ArtAl,CNT.Cont,a.Terminal)=1 AND a.[CodUnitate]=CNT.[CodUnitate]
LEFT OUTER JOIN
(SELECT  
			dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit)) Cont, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ca+''',SumaDebit)) CrediteAprobate, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@det+''',SumaDebit)) CreditDeschis, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ab+''',SumaDebit)) AngajamenteBugetare, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@al+''',SumaDebit)) AngajamenteLegale, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@plati+''',COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@chelt+''',SumaDebit)) Cheltuieli 
		FROM 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContDebit)+'','','''+@toate+''')<>0 '  + @filtData + @strFiltruCapitol +
			' AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+
		' GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>''N/A'' AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContCredit)+'','','''+@toate+''')<>0 ' + @filtDataC + @strFiltruCapitol + ' 
			AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+'
			 GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
GROUP BY dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
ON  cnt.cont=rul.cont
WHERE   a.ArtAl <>''N/A'' ' + @filtC

SET @strSelect = @strSelect + ' GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu ORDER BY a.ArtAl' 


--ON substring(rul.cont,1,len(c.cont))=c.cont


PRINT @strSelect
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				

UPDATE [#balanta_t] SET [CrediteAprobate] = (SELECT SUM([CrediteAprobate]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[CrediteDeschise] = (SELECT SUM([CrediteDeschise]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteBugetare] = (SELECT SUM([AngajamenteBugetare]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteLegale] = (SELECT SUM([AngajamenteLegale]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[PlatiNete] = (SELECT SUM([PlatiNete]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[DisponibilCredit] = (SELECT SUM([DisponibilCredit]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[Cheltuieli] = (SELECT SUM([Cheltuieli]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl)
WHERE [Categorii]='[TOT]'


SET @strSelect ='INSERT INTO [#balanta_t]
SELECT ''0'',''0'',''0'',''0'',1,0,''TOTAL'', 
	SUM(COALESCE([CrediteAprobate],0)),
	SUM(COALESCE([CrediteDeschise],0)),
	SUM(COALESCE([AngajamenteBugetare],0)),
	SUM(COALESCE([AngajamenteLegale],0)),
	SUM(COALESCE([PlatiNete],0)),
	SUM(COALESCE([DisponibilCredit],0)),
	SUM(COALESCE([Cheltuieli],0)),
	SUM(COALESCE([DebitInitial],0)),
	SUM(COALESCE([CreditInitial],0))
FROM [#balanta_t] '

SET @strSelect=@strSelect + 'WHERE terminal=1'
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE	

if @centr=0
BEGIN
	if @cfm=0
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t] 
	WHERE [CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] != 0   
	ORDER BY ArtAl
	else
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
	ORDER BY ArtAl
END
ELSE
SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
WHERE [Categorii]='[TOT]' OR Anexa6=1   
ORDER BY ArtAl

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)	
	GOTO Gata
Gata:


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetArticolAliniat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetArticolAliniat]
GO

CREATE FUNCTION [dbo].[GetArticolAliniat] (@Cont TipCont)  
RETURNS TipCont  AS  
BEGIN 
DECLARE @ret TipCont
SET @ret = ''	
IF LEN(@Cont) >=22
	BEGIN
		SET @ret = SUBSTRING(@Cont	,17,6)
		IF SUBSTRING(@ret,5,2)='00'
			SET @ret = SUBSTRING(@ret,1,4)
	END 	
return @ret	
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenListaOperatiuni]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenListaOperatiuni]
GO

CREATE PROCEDURE [dbo].[GenListaOperatiuni]   
@idLista smallint ,
@CodUnitate smallint
AS


DECLARE @strCapitol varchar(100)
DECLARE @strFNI varchar(100)
DECLARE @strFiltruCapitol varchar(100)
DECLARE @strArticol varchar(100)
DECLARE @strSelect varchar(8000)
DECLARE @strSelect2 varchar(8000)
DECLARE @strFiltru varchar(8000)
DECLARE @strFiltru2 varchar(8000)
DECLARE @idJurn int
DECLARE @fni bit
SET @fni=0
DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)


SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
--PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE [IDLista]=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataStart = RTRIM(LTRIM(@strDataStart))
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float) AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE [IDLista]=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate] = @CodUnitate
SET @strDataEnd = RTRIM(LTRIM(@strDataEnd))

IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float) AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END


SELECT @strCapitol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
--PRINT('[' + @strCapitol + ']')
SELECT @strFNI=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
if @strFNI = '1'
	SET @fni = 1
SELECT @strArticol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='AAA' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SELECT @idJurn = IDJurnal FROM [Jurnale] WHERE [Codificare]=@strCapitol  AND [CodUnitate]=@CodUnitate

--Print(@strArticol)
if @strArticol='' OR @strArticol is NULL
	GOTO EROARE1
SET @strFiltruCapitol = ''
if @idJurn<>0 AND @idJurn is NOT NULL
	SET @strFiltruCapitol = ' AND IDJurnal='+cast(@idJurn as varchar(10))


DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @aprobate varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @aprobate =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @aprobate=@aprobate + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@aprobate


CREATE TABLE [#doc_aa] (
	[Data] datetime NOT NULL, 
	[Numar] decimal(20,0) NOT NULL DEFAULT 0,
	[Explicatii] varchar(200) NOT NULL,	
	[ValoareLei] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[ContDebit] varchar(50) NOT NULL,
	[ContCredit] varchar(50) NOT NULL,	
	
	[CrediteAprobate] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrediteDeschise] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[Plati] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[Restituiri] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[CheltuieliDebit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CheltuieliCredit] DECIMAL(20,2) NOT NULL DEFAULT 0

)


SET @strSelect ='INSERT INTO [#doc_aa]
SELECT  rd.DataDoc, rd.NumarDoc, r.ExplicatiiDocJust, r.ValoareLei,dbo.GetSintetic(r.ContDebit),dbo.GetSintetic(r.ContCredit),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@aprobate+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@aprobate+''', r.ValoareLei),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@det+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@det+''', r.ValoareLei),'+
'dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@plati+''', r.ValoareLei) , dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit) +'','','''+@plati+''', r.ValoareLei),
dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@chelt+''', r.ValoareLei) , dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@chelt+''', r.ValoareLei)
FROM [Rulaje] r JOIN [RulajeDoc] rd ON r.IDDocument=rd.IDDocument '

SET @strSelect2 ='INSERT INTO [#doc_aa]
SELECT  ''1900-01-01'', 0, '''', coalesce(sum(r.ValoareLei),0) AS [ValoareLei],'''','''',
Coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@aprobate+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@aprobate+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@det+''', r.ValoareLei) - dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@det+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@plati+''', r.ValoareLei)),0) , coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit) +'','','''+@plati+''', r.ValoareLei)),0),
coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@chelt+''', r.ValoareLei)),0) , coalesce(sum(dbo.IsContInRetValoare('',''+ dbo.GetSintetic(r.ContCredit) +'','','''+@chelt+''', r.ValoareLei)),0)
FROM [Rulaje] r JOIN [RulajeDoc] rd ON r.IDDocument=rd.IDDocument '



SET @strFiltru = 'WHERE rd.[CodUnitate]='+CAST(@CodUnitate AS varchar(20)) +' AND
(dbo.GetArticolAliniat(r.ContDebit) like '''+@strArticol+'%''  OR dbo.GetArticolAliniat(r.ContCredit) like '''+@strArticol+'%'') AND
(dbo.IsContIn('',''+ dbo.GetSintetic(r.ContDebit)+'','','''+@toate+''')<>0  OR dbo.IsContIn('',''+ dbo.GetSintetic(r.ContCredit)+'','','''+@toate+''')<>0) '
IF @fni = 1
	SET @strFiltru =  @strFiltru + '  AND r.TipDocJust <>''IC'' '
IF @strFiltruCapitol<>''
	SET @strFiltru =  @strFiltru +@strFiltruCapitol
	

SET @strFiltru2 = @strFiltru

IF LEN(@strDataStart) <>0
	SET @strFiltru = @strFiltru + ' AND rd.DataDoc >='+@strDataStart
IF LEN(@strDataEnd) <>0
	SET @strFiltru = @strFiltru + ' AND rd.DataDoc <='+@strDataEnd	
SET @strFiltru =  @strFiltru +' ORDER BY rd.DataDoc'

IF LEN(@strDataStart) <>0
	SET @strFiltru2 = @strFiltru2 + ' AND rd.DataDoc <'+@strDataStart



SET @strSelect = @strSelect + LTRIM(RTRIM(@strFiltru))
SET @strSelect2 = @strSelect2 + LTRIM(RTRIM(@strFiltru2))
PRINT(@strSelect)
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				
PRINT(@strSelect2)
EXEC(@strSelect2)
IF @@ERROR != 0 GOTO EROARE				


SELECT	[Data],[Numar],[Explicatii],[CrediteAprobate],[CrediteDeschise],[Plati],[Restituiri],[CheltuieliDebit],[CheltuieliCredit]
FROM  [#doc_aa]
ORDER BY [Data]

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea listei',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Va rugam sa selectati articolul si aliniatul pentru lista',16,-1)
	GOTO Gata
EROARE2:
	RAISERROR ('Capitol incorect',16,-1)
	GOTO Gata

Gata:

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenListaExecutieChelt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenListaExecutieChelt]
GO

CREATE PROCEDURE [dbo].[GenListaExecutieChelt]   
@idLista smallint, @CodUnitate smallint, 
@An varchar(4)
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @strCapitol as varchar(100)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @tipb as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @fni bit
DECLARE @centr bit
DECLARE @idJurn smallint
DECLARE @strFiltruCapitol varchar(200)
DECLARE @strFiltruSS varchar(200)

SET @idJurn = -1
SET @categorie = 0
SET @strcategorie = '0'
SET @analitica = 0
SET @cfm = 0
SET @fni = 0
SET @centr = 0

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SET @strDataStart = LTRIM(@strDataStart)
SET @strDataStart = RTRIM(@strDataStart)
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float)-2  AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)

IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END

SELECT @strContStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SELECT @strContEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate



--conturi fara miscari
SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @cfm =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @fni =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CNT' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @centr =1

SELECT @strCapitol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SELECT @idJurn = IDJurnal FROM [Jurnale] WHERE [Codificare]=@strCapitol  AND [CodUnitate]=@CodUnitate


	
SET @strFiltruCapitol = ''
if @idJurn>0 AND @idJurn is NOT NULL
	SET @strFiltruCapitol = ' AND IDJurnal='+cast(@idJurn as varchar(10))

SET @strFiltruSS = ''
SELECT @strFiltruSS=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='SFI' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate

if LEN(RTRIM(LTRIM(@strFiltruSS)))>0 AND @idJurn is NOT NULL
	SET @strFiltruSS = ' AND (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters('''+@strFiltruSS+''','','') fart))'	



IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @ca=@ca + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @ab=@ab + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate
SELECT @al=@al + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate 
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@ca+@ab+@al


CREATE TABLE [#balanta_t] (
	[ArtAl] varchar(50) NOT NULL, 
	[AAParinte] varchar(50) NOT NULL, 
	[Categorii] varchar(50) NOT NULL, 
	[Denumire] varchar(400) NOT NULL DEFAULT '',
	[Anexa6] bit NOT NULL DEFAULT 0,	
	[Terminal] bit NOT NULL,
	[Titlu] varchar(100) NOT NULL,

	[CrediteAprobate] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrediteDeschise] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteBugetare] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteLegale] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[PlatiNete] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DisponibilCredit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[Cheltuieli] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[DebitInitial] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreditInitial] DECIMAL(20,2) NOT NULL DEFAULT 0
)

DECLARE @strSelect	varchar(8000)
DECLARE @filtData varchar(500)
DECLARE @filtDataC varchar(500)
DECLARE @filtC varchar(500)
DECLARE @filtCC varchar(500)
DECLARE @strCodUnitate varchar(50)
DECLARE @arh varchar(4)
DECLARE @filtruArh varchar(300)
DECLARE @filtruArhRulajeDoc varchar(300)


SET @filtData = ''
SET @filtDataC = ''
SET @filtC = ''
SET @filtCC = ''
SET @strCodUnitate = CAST(@CodUnitate AS varchar(50))

SET @arh = ''
SET @filtruArh=''
SET @filtruArhRulajeDoc=''
IF @An<>''
BEGIN
	SET @arh='arh'
	SET @filtruArh=' AND An=' + @An
	SET @filtruArhRulajeDoc=' AND D.An=' + @An
END	

IF @idLista = 1
	BEGIN
		IF LEN(@strDataEnd) <>0
			SET @filtData =  ' AND D.DataDoc <='+@strDataEnd		
	END
ELSE
	BEGIN
		IF LEN(@strDataStart) <>0
			SET @filtData =  ' AND D.DataDoc >='+@strDataStart
		IF LEN(@strDataEnd) <>0
			SET @filtData = @filtData + '  AND D.DataDoc <='+@strDataEnd	
	END
IF LEN(@strContStart) <>0
	SET @filtC =  '  AND F.ContDebit >='''+@strContStart + ''' '
IF LEN(@strContEnd) <>0
	SET @filtC = @filtC + ' AND F.ContDebit <='''+@strContEnd + ''' '

SET @filtData = @filtData + @filtC
IF @fni = 1
	SET @filtData =@filtData + '  AND F.TipDocJust <>''IC'' '  

SET @filtDataC = @filtData
SET @filtDataC = REPLACE(@filtDataC,'F.ContDebit','F.ContCredit') 
PRINT 'Total' + @filtDataC
SET @filtCC = REPLACE(@filtC,'F.ContDebit','Cont') 
SET @filtC = REPLACE(@filtC,'F.ContDebit','a.ArtAl') 

--	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
--	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
--	Cnt.[DebitInitial],Cnt.[CreditInitial]

SET @strSelect =' INSERT INTO [#balanta_t]
	 SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu,
	SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
	SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
	SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
	SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
	SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
	SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
	SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
FROM [ArticoleAliniate] a LEFT OUTER JOIN 
(SELECT 
dbo.GetAnalitic(Cont) Cont,[CodUnitate],
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',DebitInitial)) DebitInitial,
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',CreditInitial)) CreditInitial
FROM
['+@arh+'Conturi]
WHERE Terminal = 1 AND Cont<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(Cont)+'','','''+@toate+''')<>0 ' + @filtCC + '
 AND [CodUnitate] = '+@strCodUnitate+@filtruArh +
 @strFiltruSS + 
 'GROUP BY dbo.GetAnalitic(Cont),[CodUnitate]) CNT
ON dbo.IsContInclus(a.ArtAl,CNT.Cont,a.Terminal)=1 AND a.[CodUnitate]=CNT.[CodUnitate]
LEFT OUTER JOIN
(SELECT  
			dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit)) Cont, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ca+''',SumaDebit)) CrediteAprobate, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@det+''',SumaDebit)) CreditDeschis, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ab+''',SumaDebit)) AngajamenteBugetare, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@al+''',SumaDebit)) AngajamenteLegale, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@plati+''',COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@chelt+''',SumaDebit)) Cheltuieli 
		FROM 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContDebit)+'','','''+@toate+''')<>0 '  + @filtData + @strFiltruCapitol +
			' AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+
		' GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>''N/A'' AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContCredit)+'','','''+@toate+''')<>0 ' + @filtDataC + @strFiltruCapitol + ' 
			AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+'
			 GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
GROUP BY dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
ON  cnt.cont=rul.cont
WHERE   a.ArtAl <>''N/A'' ' + @filtC

SET @strSelect = @strSelect + ' GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu' 


--ON substring(rul.cont,1,len(c.cont))=c.cont


PRINT @strSelect
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				

UPDATE [#balanta_t] SET [CrediteAprobate] = (SELECT SUM([CrediteAprobate]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[CrediteDeschise] = (SELECT SUM([CrediteDeschise]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteBugetare] = (SELECT SUM([AngajamenteBugetare]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteLegale] = (SELECT SUM([AngajamenteLegale]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[PlatiNete] = (SELECT SUM([PlatiNete]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[DisponibilCredit] = (SELECT SUM([DisponibilCredit]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[Cheltuieli] = (SELECT SUM([Cheltuieli]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl)
WHERE [Categorii]='[TOT]'


SET @strSelect ='INSERT INTO [#balanta_t]
SELECT ''0'',''0'',''0'',''0'',1,0,''TOTAL'', 
	SUM(COALESCE([CrediteAprobate],0)),
	SUM(COALESCE([CrediteDeschise],0)),
	SUM(COALESCE([AngajamenteBugetare],0)),
	SUM(COALESCE([AngajamenteLegale],0)),
	SUM(COALESCE([PlatiNete],0)),
	SUM(COALESCE([DisponibilCredit],0)),
	SUM(COALESCE([Cheltuieli],0)),
	SUM(COALESCE([DebitInitial],0)),
	SUM(COALESCE([CreditInitial],0))
FROM [#balanta_t] '

SET @strSelect=@strSelect + 'WHERE terminal=1'
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE	

if @centr=0
BEGIN
	if @cfm=0
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t] 
	WHERE [CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] != 0   
	ORDER BY ArtAl
	else
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
	ORDER BY ArtAl
END
ELSE
SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
WHERE [Categorii]='[TOT]' OR Anexa6=1   
ORDER BY ArtAl

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)	
	GOTO Gata
Gata:

GO




IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewRegistru]'))
DROP VIEW [dbo].[AmixViewRegistru]
GO

CREATE VIEW [dbo].[AmixViewRegistru]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
                      dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
                      dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, COALESCE (dbo.AmixAmortizare.ValoareInventar, dbo.AmixRegistru.ValoareInventar) 
                      AS ValoareInventar, COALESCE (dbo.AmixAmortizare.ValoareAmortizata, dbo.AmixRegistru.ValoareInventar) AS ValoareAmortizata, 
                      COALESCE (dbo.AmixAmortizare.ValoareRamasa, 0) AS ValoareRamasa, dbo.AmixAmortizare.AmortizareLuna, dbo.AmixAmortizare.DurataNormata, 
                      dbo.AmixAmortizare.LuniAmortizate, dbo.AmixTipStare.Denumire AS Stare, COALESCE (dbo.AmixAmortizare.Luna, 0) AS Luna, COALESCE (dbo.AmixAmortizare.An, 0) 
                      AS An, dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, COALESCE (dbo.AmixAmortizare.Blocat, 0) AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, 
                      dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie, dbo.AmixCatalog.Grupa, 
                      dbo.Departamente.IDDepartament, dbo.AmixRegistru.ContTransferForSuperior, dbo.AmixRegistru.ValoareIntrataTransfer, dbo.Jurnale.Codificare, 
                      dbo.AmixRegistru.ContTransfer, dbo.AmixRegistru.IDSursaFinantare,dbo.SurseFinantare.Denumire AS SursaFinantare
FROM         dbo.AmixRegistru LEFT OUTER JOIN
                      dbo.SurseFinantare ON dbo.AmixRegistru.IDSursaFinantare = dbo.SurseFinantare.IDSursaFinantare LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.AmixRegistru.IDJurnal = dbo.Jurnale.IDJurnal LEFT OUTER JOIN
                      dbo.AmixAmortizare ON dbo.AmixAmortizare.IDMijlocFix = dbo.AmixRegistru.IDMijlocFix LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare LEFT OUTER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare LEFT OUTER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_spViewRegistru]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_spViewRegistru]
GO

CREATE PROCEDURE [dbo].[amix_spViewRegistru]
	-- Add the parameters for the stored procedure here
	@filtru varchar(1000), 
	@an smallint,
	@luna smallint,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @PerCurenta decimal(10)
	SELECT @PerCurenta = AnulCurent*100+LunaCurenta FROM AmixSetup WHERE CodUnitate=@CodUnitate
	DECLARE @Arhiva bit

	SET @Arhiva=0
	IF @PerCurenta>@An*100+@Luna
		SET @Arhiva= 1

    DECLARE @cmd varchar(8000)
    SET @cmd =
	' SELECT  *  FROM (SELECT  ar.NrInventar, ar.Denumire, ar.ContAmortizare, ar.DataIntrare, ar.DataIesire, 
                      ar.CodUnitate,  ar.ContImobilizare, ar.ContCheltuiala, ar.DurataAmortizare, ar.IDMijlocFix,
                      ar.RegimAmortizare, ar.ValoareInventar, 
                      ar.Compus,ar.StocFaptic, ar.Categorie,ar.Blocat,ar.IDStare,
                      dbo.Departamente.Nume,
                      COALESCE (aa.ValoareAmortizata,CASE WHEN YEAR(ar.PunereInFunctiune)*100 + MONTH(ar.PunereInFunctiune) < '+CAST(@An*100+@luna AS varchar(6))+' THEN ar.ValoareInventar ELSE ar.ValoareAmortizata END) AS ValoareAmortizata, 
                      COALESCE (aa.ValoareRamasa, 0) AS ValoareRamasa, COALESCE (aa.AmortizareLuna, 0) AS AmortizareLuna, COALESCE (aa.DurataNormata, 0) 
                      AS DurataNormata, aa.LuniAmortizate, COALESCE (aa.Luna, 0) AS Luna, COALESCE (aa.An, 0) AS An, 
                      COALESCE (aa.Blocat, 0) AS AmortizareBlocata,
                      dbo.AmixTipStare.Denumire AS Stare, 
                      dbo.AmixGestionari.Nume AS Gestionar, 
                      dbo.AmixCatalog.Cod,dbo.AmixCatalog.Grupa,ContTransferForSuperior, ValoareIntrataTransfer,j.Codificare,ar.ContTransfer,ar.IDSursaFinantare,
                      ss.Denumire as SursaFinantare
		FROM  '
		
		IF @Arhiva = 1
			SET @cmd = @cmd + '(SELECT *  FROM dbo.AmixRegistruBK WHERE Luna='+CAST(@Luna as varchar(4)) + ' AND An=' + CAST(@an as varchar(4)) + ')'
		ELSE
			SET @cmd = @cmd + 'dbo.AmixRegistru ' 
		SET @cmd = @cmd +' ar 
					  LEFT OUTER JOIN SurseFinantare ss ON ar.IDSursaFinantare=ss.IDSursaFinantare LEFT OUTER JOIN
					  (SELECT * FROM AmixAmortizare aa WHERE aa.Luna='+CAST(@Luna AS varchar(2))+' and aa.An= ' + cast(@An as varchar(4)) + ') aa  ON ar.IDMijlocFix = aa.IDMijlocFix 
                      LEFT OUTER JOIN  dbo.AmixCatalog ON ar.IDClasificare = dbo.AmixCatalog.IDClasificare 
                      LEFT OUTER JOIN dbo.Departamente ON ar.IDDepartament = dbo.Departamente.IDDepartament 
                      LEFT OUTER JOIN dbo.AmixTipStare ON ar.IDStare = dbo.AmixTipStare.IDStare 
					  LEFT OUTER JOIN dbo.Jurnale j ON ar.IDJurnal = j.IDJurnal 
                      LEFT OUTER JOIN dbo.AmixGestionari ON ar.IDGestionar = dbo.AmixGestionari.IDGestionar) fs '
     IF @filtru <> ''
		SET @cmd = @cmd + ' WHERE ' + @filtru
     EXEC(@cmd) 
 
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenListaExecutieChelt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenListaExecutieChelt]
GO



CREATE PROCEDURE [dbo].[balcon_GenListaExecutieChelt]   
@idLista smallint, 
@deLaData datetime,
@panaLaData datetime,
@contStart  dbo.TipCont,
@contEnd  dbo.TipCont,
@CodUnitate smallint, 
@strFiltruNrNota varchar(2000),
@centralizat bit,
@cfm bit,
@filtruSS varchar(500),
@filtruJurnal varchar(2000),
@An varchar(4)
AS



DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @ca=@ca + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @ab=@ab + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate
SELECT @al=@al + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate 
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@ca+@ab+@al

PRINT @det
PRINT @ca
PRINT @ab
PRINT @al
Print @plati
Print @chelt
PRINT @toate

--DROP TABLE #TempExecChelt

SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire Denumire,a.Anexa6,a.Terminal,a.Titlu,
      SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
      SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
      SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
      SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
      SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
      SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
      SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
      SUM(COALESCE(Cnt.[DebitInitial],0)) DebitInitial,
      SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
INTO #TempExecChelt
FROM (SELECT * FROM [ArticoleAliniate] WITH(NOLOCK) WHERE CodUnitate=@CodUnitate) a 
LEFT OUTER JOIN
(SELECT --pentru soldurile initiale la angajamente legale
      dbo.GetArticolAliniat(Cont) ArtAlCont,[CodUnitate],
      sum(DebitInitial) DebitInitial,
      sum(CreditInitial) CreditInitial
      FROM [Conturi] WITH(NOLOCK)
      WHERE [CodUnitate] = @CodUnitate AND dbo.IsContIn(','+ dbo.GetSintetic(Cont)+',',@al)<>0 
            AND (LEN(@contStart)=0 OR Cont>=@contStart)
            AND (LEN(@contEnd)=0 OR Cont<=@contEnd)
            AND Terminal = 1
      GROUP BY dbo.GetArticolAliniat(Cont),[CodUnitate]
  ) CNT
ON a.ArtAl=CNT.ArtAlCont AND a.[CodUnitate]=CNT.[CodUnitate]
LEFT OUTER JOIN 
(SELECT  
                  dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit)) ArtAlCont, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ca,SumaDebit)) CrediteAprobate, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@det,SumaDebit)) CreditDeschis, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ab,SumaDebit)) AngajamenteBugetare, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@al,SumaDebit)) AngajamenteLegale, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@plati,COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
                  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@chelt,SumaDebit)) Cheltuieli 
            FROM 
                  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
                  FROM [RulajeDoc] D  INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate 
                  WHERE 
                  F.ContDebit<>'N/A'  AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContDebit)+',',@toate)<>0  
                  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData
                  AND (LEN(@contStart)=0  OR ContDebit>=@contStart)
                  AND (LEN(@contEnd)=0  OR ContDebit<=@contEnd)
                  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))             
                  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContDebit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
                  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
                GROUP BY F.ContDebit) t1 

              FULL OUTER JOIN 
                  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
                  FROM [RulajeDoc] D WITH(NOLOCK)  INNER JOIN [Rulaje] F  WITH(NOLOCK) ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate 
                  WHERE 
                  F.ContCredit <>'N/A' AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContCredit)+',',@toate)<>0  
                  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData 
                  AND (LEN(@contStart)=0  OR ContCredit>=@contStart)
                  AND (LEN(@contEnd)=0  OR ContCredit<=@contEnd)
                  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
                  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContCredit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
                  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
                  GROUP BY F.ContCredit) t2 
            ON t1.ContDebit = t2.ContCredit 
WHERE dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))<>'XXXXXX'            
GROUP BY dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
ON  rul.ArtAlCont = LTRIM(RTRIM(a.ArtAl)) 
WHERE   a.ArtAl <>'N/A'  
GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu

UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a   GROUP BY AAParinte )  d
WHERE d.AAParinte=#TempExecChelt.ArtAl AND
LEN(ArtAl)=4 AND [Categorii]<>'[TOT]' AND Terminal=0



UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d
WHERE d.AAParinte=#TempExecChelt.ArtAl AND LEN(ArtAl)=2 AND [Categorii]<>'[TOT]' AND Terminal=0


UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d 
WHERE d.AAParinte=#TempExecChelt.ArtAl AND [Categorii]='[TOT]'





SELECT * FROM #TempExecChelt
where ((@centralizat=0) OR ([Categorii]='[TOT]' OR Anexa6=1))  
AND (@cfm=1 OR ([CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] <> 0))
ORDER BY ArtAl


GOTO Gata
EROARE:
      RAISERROR ('Eroare la generarea balantei',16,-1)
      GOTO Gata
EROARE1:
      RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)      
      GOTO Gata
Gata:


GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewAll]'))
DROP VIEW [dbo].[AmixViewAll]
GO


CREATE VIEW [dbo].[AmixViewAll]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
                      dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
                      dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, dbo.AmixRegistru.ValoareInventar, dbo.AmixRegistru.ValoareAmortizata, 
                      dbo.AmixRegistru.ValoareInventar - dbo.AmixRegistru.ValoareAmortizata AS ValoareRamasa, 0 AS AmortizareLuna, 
                      dbo.AmixRegistru.DurataAmortizare AS DurataNormata, dbo.AmixRegistru.LuniAmortizate, dbo.AmixTipStare.Denumire AS Stare, 0 AS Luna, 0 AS An, 
                      dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, 0 AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, dbo.AmixGestionari.Nume AS Gestionar, 
                      dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie, dbo.AmixCatalog.Grupa, dbo.AmixRegistru.ContTransferForSuperior, 
                      dbo.AmixRegistru.ValoareIntrataTransfer, dbo.Jurnale.Codificare, dbo.AmixRegistru.ContTransfer, dbo.AmixRegistru.IDSursaFinantare, 
                      dbo.SurseFinantare.Denumire AS SursaFinantare
FROM         dbo.AmixRegistru LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare INNER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament INNER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare INNER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar LEFT OUTER JOIN
                      dbo.SurseFinantare ON dbo.AmixRegistru.IDSursaFinantare = dbo.SurseFinantare.IDSursaFinantare LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.AmixRegistru.IDJurnal = dbo.Jurnale.IDJurnal

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ExBugSetup' AND COLUMN_NAME = 'ContO')
BEGIN
	ALTER TABLE dbo.ExBugSetup ADD ContO dbo.TipCont NULL

	ALTER TABLE [dbo].[ExBugSetup]  WITH CHECK ADD CONSTRAINT [FK_ExBugSetup_Conturi] FOREIGN KEY([ContO],[CodUnitate]) REFERENCES [dbo].[conturi] ([Cont],[CodUnitate])
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ExBugSetup' AND COLUMN_NAME = 'GenereazaNCOrdonantare')
BEGIN
	ALTER TABLE dbo.ExBugSetup ADD GenereazaNCOrdonantare bit NOT NULL DEFAULT 0
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Ordonantari' AND COLUMN_NAME = 'IDSursaFinantare')
BEGIN
	ALTER TABLE dbo.Ordonantari ADD IDSursaFinantare smallint NULL
	ALTER TABLE dbo.arhOrdonantari ADD IDSursaFinantare smallint NULL

	ALTER TABLE dbo.Ordonantari ADD CONSTRAINT
		FK_Ordonantari_SurseFinantare FOREIGN KEY
		(IDSursaFinantare) REFERENCES dbo.SurseFinantare (IDSursaFinantare) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 

END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrdonantariNC]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[OrdonantariNC](
	[IDOrdonantare] [int] NOT NULL,
	[IDDocument] [bigint] NOT NULL,
 CONSTRAINT [PK_OrdonantariNC] PRIMARY KEY CLUSTERED ([IDOrdonantare] ASC,[IDDocument] ASC),
 CONSTRAINT [FK_OrdonantariNC_Ordonantari] FOREIGN KEY([IDOrdonantare]) REFERENCES [dbo].[Ordonantari] ([IdOrdonantare]),
 CONSTRAINT [FK_OrdonantariNC_RulajeDoc] FOREIGN KEY([IDDocument]) REFERENCES [dbo].[RulajeDoc] ([IDDocument])
) 

END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhOrdonantariNC]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[arhOrdonantariNC](
	[IDOrdonantare] [int] NOT NULL,
	[IDDocument] [bigint] NOT NULL,
 CONSTRAINT [PK_arhOrdonantariNC] PRIMARY KEY CLUSTERED ([IDOrdonantare] ASC,[IDDocument] ASC)
 
) 

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_OrdonantareGenNotaContabila]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_OrdonantareGenNotaContabila]
GO

-- =============================================
-- Author:		Popa Dorin	
-- Create date: 30.03.2014
-- Description:	Genereaza nota contabila pentru ordonantare
-- =============================================
CREATE PROCEDURE [dbo].[exbug_OrdonantareGenNotaContabila]
	-- Add the parameters for the stored procedure here
	@idOrdonantare int,
	@CodUnitate smallint
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      DECLARE @ContOrdonantare dbo.TipCont
      DECLARE @ContOrdAnal dbo.TipCont    
      DECLARE @genereaza bit
      SET @genereaza = 0

      SELECT @ContOrdonantare = ContO, @genereaza = GenereazaNCOrdonantare  FROM ExBugSetup WHERE CodUnitate=@CodUnitate
      
      IF @genereaza = 0
            RETURN
            
            
      IF @ContOrdonantare is null OR @ContOrdonantare='' OR @ContOrdonantare='N/A'
            GOTO EROARE2
    -- Insert statements for procedure here
    
    --Compune contul
    SELECT @ContOrdAnal = @ContOrdonantare + '01' + ss.Cod + REPLACE(j.Codificare,'.','') + 
    CASE WHEN LEN(REPLACE(o.ArtAl,'.',''))=4 THEN REPLACE(o.ArtAl,'.','') +'00' ELSE REPLACE(o.ArtAl,'.','') END
    FROM Ordonantari o JOIN SurseFinantare ss ON o.IDSursaFinantare = ss.IDSursaFinantare
    JOIN Jurnale j ON o.IDJurnal = j.IDJurnal
    WHERE IdOrdonantare = @idOrdonantare
	PRINT @ContOrdAnal
	DECLARE @idNotaInsert bigint

    BEGIN TRAN
    --adauga contul 
    IF NOT EXISTS(SELECT * FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @ContOrdAnal)
    BEGIN
			PRINT 'Adauga cont'
            INSERT INTO [Conturi]
           ([Cont]
           ,[CodUnitate]
           ,[Denumire]
           ,[Tip]
           ,[Terminal]
           ,[ContParinte]
           ,[EsteAnalitic]
           ,[DebitInitial]
           ,[CreditInitial]
           ,[DebitPrecedent]
           ,[CreditPrecedent]
           ,[Categorie]
           ,[RulajDebit]
           ,[RulajCredit]
           ,[Sold])
     SELECT 
           @ContOrdAnal
           ,CodUnitate
           ,Denumire
           ,Tip
           ,1 Terminal
           ,@ContOrdonantare ContParinte
           ,1 EsteAnalitic
           ,0
           ,0
           ,0
           ,0
           ,Categorie
           ,0
           ,0
           ,0
           FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @ContOrdonantare
    END
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulaje'
	DELETE FROM Rulaje 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc  WHERE  Rulaje.IDDocument= onc.IDDocument AND onc.IDOrdonantare = @idOrdonantare)
	IF @@ERROR != 0 GOTO EROARE
	

	PRINT 'Sterge Ordonantari NC'
	DELETE FROM OrdonantariNC WHERE IDOrdonantare = @idOrdonantare
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulajedoc'
	DELETE FROM RulajeDoc 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc  WHERE  RulajeDoc.IDDocument= onc.IDDocument AND onc.IDOrdonantare = @idOrdonantare)
	IF @@ERROR != 0 GOTO EROARE

	


    INSERT INTO [RulajeDoc]
           ([ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate]
           ,[CodUnitate]
           ,[IDSursaFinantare]
           ,[IDSectorBugetar])
     SELECT
            ValoareLei
           ,0 ValoareValuta
           ,(SELECT MAX(NumarDoc)+1 FROM RulajeDoc WHERE CodUnitate=@CodUnitate) NumarDoc
           ,DataDoc
           ,'NC' NumarNota
           ,3 TipFormula
           ,IDJurnal
           ,Scop Observatii
           ,suser_sname() xUser
           ,host_name() xHost
           ,GETDATE() xDate
           ,CodUnitate
           ,IDSursaFinantare
           ,NULL IDSectorBugetar FROM Ordonantari WHERE IdOrdonantare = @idOrdonantare
    IF @@ERROR != 0 GOTO EROARE
	SET @idNotaInsert = @@IDENTITY

	INSERT INTO [Rulaje]
           ([ContDebit]
           ,[ContCredit]
           ,[CodValuta]
           ,[IDDocument]
           ,[ValoareValuta]
           ,[Curs]
           ,[ValoareLei]
           ,[DataDocJust]
           ,[TipDocJust]
           ,[NumarDocJust]
           ,[ExplicatiiDocJust]
           ,[NrCrt]
           ,[CodUnitate])
     SELECT
           @ContOrdAnal ContDebit
           ,'N/A' ContCredit
           ,0 CodValuta
           ,@idNotaInsert IDDocument
           ,0 ValoareValuta
           ,0.0000 Curs
           ,ValoareLei
           ,DataDoc DataDocJust
           ,'ORD' TipDocJust
           ,NrDoc NumarDocJust
           ,Scop ExplicatiiDocJust
           ,0 NrCrt
           ,CodUnitate FROM Ordonantari WHERE IdOrdonantare = @idOrdonantare
		  IF @@ERROR != 0 GOTO EROARE

		 INSERT INTO [OrdonantariNC]
			   ([IDOrdonantare]
			   ,[IDDocument])
		 VALUES
			   (@idOrdonantare,@idNotaInsert)
	     IF @@ERROR != 0 GOTO EROARE

	  COMMIT	
      GOTO Gata
      EROARE:
            ROLLBACK
            RAISERROR ('Eroare la generare notei contabile pt ordonantare datelor!',16,-1)
            CLOSE crsSpor
            DEALLOCATE crsSpor
            GOTO Gata
      EROARE1:
            RAISERROR ('!!',16,-1)  
            GOTO Gata
      EROARE2:
            RAISERROR ('Nu a fost definit contul pentru generearea ordonantarii',16,-1)   
            GOTO Gata         
      Gata:
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_OrdonantareGenNotaContabilaGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_OrdonantareGenNotaContabilaGroup]
GO


-- =============================================
-- Author:		Popa Dorin	
-- Create date: 30.03.2014
-- Description:	Genereaza nota contabila pentru ordonantare
-- =============================================
CREATE PROCEDURE [dbo].[exbug_OrdonantareGenNotaContabilaGroup]
	-- Add the parameters for the stored procedure here
	@idOrdonantareGrup varchar(8000),
	@CodUnitate smallint
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      --SET NOCOUNT ON;
      DECLARE @ContOrdonantare dbo.TipCont
      DECLARE @ContOrdAnal varchar(8000)   
      DECLARE @genereaza bit
      SET @genereaza = 0

      SELECT @ContOrdonantare = ContO, @genereaza = GenereazaNCOrdonantare  FROM ExBugSetup WHERE CodUnitate=@CodUnitate
      
      IF @genereaza = 0
            RETURN
            
            
      IF @ContOrdonantare is null OR @ContOrdonantare='' OR @ContOrdonantare='N/A'
            GOTO EROARE2
    -- Insert statements for procedure here
    SET @ContOrdAnal = ''
    --Compune contul
    SELECT @ContOrdAnal = @ContOrdAnal +  @ContOrdonantare + '01' + ss.Cod + REPLACE(j.Codificare,'.','') + 
    CASE WHEN LEN(REPLACE(o.ArtAl,'.',''))=4 THEN REPLACE(o.ArtAl,'.','') +'00' ELSE REPLACE(o.ArtAl,'.','') END + ','
    FROM Ordonantari o JOIN SurseFinantare ss ON o.IDSursaFinantare = ss.IDSursaFinantare
    JOIN Jurnale j ON o.IDJurnal = j.IDJurnal
    WHERE  IdOrdonantare IN (SELECT fart.value FROM dbo.SplitParameters(@idOrdonantareGrup,',') fart)
    --IdOrdonantare = @idOrdonantare
    
	
	--elimina ultima virgula
	SET @ContOrdAnal = SUBSTRING(@ContOrdAnal,1,LEN(@ContOrdAnal)-1)
	PRINT @ContOrdAnal
	DECLARE @idNotaInsert bigint

    BEGIN TRAN
    --adauga contul 
    IF EXISTS(SELECT DISTINCT value,cont FROM dbo.SplitParameters(@ContOrdAnal,',') fart LEFT OUTER JOIN 
    Conturi a ON LTRIM(RTRIM(fart.value))=LTRIM(RTRIM(a.cont)) AND a.CodUnitate=@CodUnitate AND Terminal=1 AND Cont is null)
    BEGIN
			PRINT 'Adauga cont'
            INSERT INTO [Conturi]
           ([Cont]
           ,[CodUnitate]
           ,[Denumire]
           ,[Tip]
           ,[Terminal]
           ,[ContParinte]
           ,[EsteAnalitic]
           ,[DebitInitial]
           ,[CreditInitial]
           ,[DebitPrecedent]
           ,[CreditPrecedent]
           ,[Categorie]
           ,[RulajDebit]
           ,[RulajCredit]
           ,[Sold])
     SELECT 
           a.Value Cont
           ,@CodUnitate
           ,(SELECT Denumire FROM Conturi WHERE Cont=@ContOrdonantare) Denumire
           ,(SELECT Tip FROM Conturi WHERE Cont=@ContOrdonantare) Tip
           ,1 Terminal
           ,@ContOrdonantare ContParinte
           ,1 EsteAnalitic
           ,0
           ,0
           ,0
           ,0
           ,(SELECT Categorie FROM Conturi WHERE Cont=@ContOrdonantare) Categorie
           ,0
           ,0
           ,0
           FROM 
           (SELECT DISTINCT value FROM dbo.SplitParameters(@ContOrdAnal,',') fart LEFT OUTER JOIN 
				Conturi a ON LTRIM(RTRIM(fart.value))=LTRIM(RTRIM(a.cont)) AND a.CodUnitate=@CodUnitate AND Terminal=1 AND Cont is null) a
			WHERE NOT EXISTS(SELECT * FROM Conturi WHERE Cont=a.value AND CodUnitate=@CodUnitate)	
           
          
    END
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulaje'
	DELETE FROM Rulaje 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc JOIN  dbo.SplitParameters(@idOrdonantareGrup,',') fart
	ON fart.value=onc.IDOrdonantare
	WHERE  Rulaje.IDDocument= onc.IDDocument)
	IF @@ERROR != 0 GOTO EROARE
	

	PRINT 'Sterge Ordonantari NC'
	DELETE FROM OrdonantariNC WHERE 
	IDOrdonantare IN (SELECT fart.value FROM dbo.SplitParameters(@idOrdonantareGrup,',') fart)
	--IDOrdonantare = @idOrdonantare
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulajedoc'
	DELETE FROM RulajeDoc 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc JOIN  dbo.SplitParameters(@idOrdonantareGrup,',') fart
	ON fart.value=onc.IDOrdonantare
	WHERE  RulajeDoc.IDDocument= onc.IDDocument)
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Insert rulajedoc'
    INSERT INTO [RulajeDoc]
           ([ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate]
           ,[CodUnitate]
           ,[IDSursaFinantare]
           ,[IDSectorBugetar])
     SELECT
            SUM(ValoareLei) ValoareLei
           ,0 ValoareValuta
           ,(SELECT MAX(NumarDoc)+1 FROM RulajeDoc WHERE CodUnitate=@CodUnitate) NumarDoc
           ,CAST(YEAR(GETDATE()) AS varchar(4)) + '-' + CAST(MONTH(GETDATE()) AS varchar(2)) + '-'+
           CAST(DAY(GETDATE()) AS varchar(2)) DataDoc
           ,'NC' NumarNota
           ,3 TipFormula
           ,MAX(IDJurnal) IDJurnal
           ,'Nota contabila generata' Observatii
           ,suser_sname() xUser
           ,host_name() xHost
           ,GETDATE() xDate
           ,MAX(CodUnitate) CodUnitate
           ,MAX(IDSursaFinantare) IDSursaFinantare
           ,NULL IDSectorBugetar 
           FROM Ordonantari WHERE IDOrdonantare IN (SELECT fart.value FROM dbo.SplitParameters(@idOrdonantareGrup,',') fart)
    IF @@ERROR != 0 GOTO EROARE
	SET @idNotaInsert = @@IDENTITY
    PRINT 'Insert rulaje'
	INSERT INTO [Rulaje]
           ([ContDebit]
           ,[ContCredit]
           ,[CodValuta]
           ,[IDDocument]
           ,[ValoareValuta]
           ,[Curs]
           ,[ValoareLei]
           ,[DataDocJust]
           ,[TipDocJust]
           ,[NumarDocJust]
           ,[ExplicatiiDocJust]
           ,[NrCrt]
           ,[CodUnitate])
     SELECT
           @ContOrdonantare + '01' + ss.Cod + REPLACE(j.Codificare,'.','') + 
    CASE WHEN LEN(REPLACE(o.ArtAl,'.',''))=4 THEN REPLACE(o.ArtAl,'.','') +'00' ELSE REPLACE(o.ArtAl,'.','') END ContDebit
           ,'N/A' ContCredit
           ,0 CodValuta
           ,@idNotaInsert IDDocument
           ,0 ValoareValuta
           ,0.0000 Curs
           ,ValoareLei
           ,DataDoc DataDocJust
           ,'ORD' TipDocJust
           ,NrDoc NumarDocJust
           ,SUBSTRING(Scop,1,200) ExplicatiiDocJust
           ,0 NrCrt
           ,o.CodUnitate FROM Ordonantari o
			JOIN SurseFinantare ss ON o.IDSursaFinantare = ss.IDSursaFinantare
			JOIN Jurnale j ON o.IDJurnal = j.IDJurnal
           
           WHERE IDOrdonantare IN (SELECT fart.value FROM dbo.SplitParameters(@idOrdonantareGrup,',') fart)
		  IF @@ERROR != 0 GOTO EROARE

		 PRINT 'Insert [OrdonantariNC]'
		 INSERT INTO [OrdonantariNC]
			   ([IDOrdonantare]
			   ,[IDDocument])
		 SELECT fart.value,@idNotaInsert FROM dbo.SplitParameters(@idOrdonantareGrup,',') fart	   
		 
	     IF @@ERROR != 0 GOTO EROARE

	  COMMIT	
      GOTO Gata
      EROARE:
            ROLLBACK
            RAISERROR ('Eroare la generare notei contabile pt ordonantare datelor!',16,-1)
            CLOSE crsSpor
            DEALLOCATE crsSpor
            GOTO Gata
      EROARE1:
            RAISERROR ('!!',16,-1)  
            GOTO Gata
      EROARE2:
            RAISERROR ('Nu a fost definit contul pentru generearea ordonantarii',16,-1)   
            GOTO Gata         
      Gata:
END


GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_GenereazaPACsiAB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_GenereazaPACsiAB]
GO


CREATE PROCEDURE [dbo].[exbug_GenereazaPACsiAB]
	-- Add the parameters for the stored procedure here
	@IDDocument bigint,
	@CodUnitate smallint,
	@IDPAC int OUT,
	@IDAB int OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @IDResponsabil int
	SET @IDResponsabil = 0
	
	SELECT TOP 1 @IDResponsabil = [IDResponsabil] FROM ResponsabilVizaCFP WHERE [CodUnitate] = @CodUnitate
	IF @IDResponsabil = 0
		GOTO Eroare

	--Propunere angajare cheltuiala
    INSERT INTO [PropunereAngajareCheltuiala]
           ([Numar]
           ,[Data]
           ,[Compartiment]
           ,[Scopul]
           ,[Beneficiarul]
           ,[CodUnitate]
           ,[SumaTotala]
           ,[VizatCFP]
           ,[MotivRefuz]
           ,[IDResponsabil]
           ,[DataVizaCFP]
           ,[NrRegCFP]
           ,[OrdonatorCredite])
     SELECT CAST(NumarDoc as varchar(20)) [Numar],
		[DataDoc],
		[CompartimentSpecialitate],
		COALESCE([Observatii],''),
		[Linia1Stanga],
		rd.[CodUnitate],
		ValoareLei,
		1,'',@IDResponsabil,GETDATE(),'',[OrdonatorCredite]
      FROM [RulajeDoc] rd JOIN ExBugSetup ebs ON rd.CodUnitate = ebs.CodUnitate
       JOIN BalconSetup bcs ON bcs.CodUnitate = rd.CodUnitate
			 WHERE [IDDocument]=@IDDocument 
           
	SET @IDPAC= SCOPE_IDENTITY()

	INSERT INTO [PropunereAngajareCheltuialaDetaliu]
           ([IDPac]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[CredBugAprob]
           ,[CredBugAngaj]
           ,[Clasif])
     SELECT @IDPAC,sd.IDJurnal,dbo.GetArticolAliniat(ContDebit) ArtAl ,
     CASE WHEN rl.ContDebit like ContAB +'%' THEN rl.valoareLei ELSE  -rl.valoareLei END,0,0,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND ((rl.ContDebit like ContAB +'%') OR (rl.ContCredit like ContAB +'%'))

	

	--Angajament bugetar
    INSERT INTO [AngajamentBugetar]
           ([Numar]
           ,[Data]
           ,[Compartiment]
           ,[Scopul]
           ,[Beneficiarul]
           ,[CodUnitate]
           ,[SumaTotala]
           ,[VizatCFP]
           ,[MotivRefuz]
           ,[IDResponsabil]
           ,[DataVizaCFP]
           ,[NrRegCFP]
           ,[OrdonatorCredite],
           [TipAngajament])
     SELECT CAST(NumarDoc as varchar(20)) [Numar],
		[DataDoc],
		[CompartimentSpecialitate],
		COALESCE([Observatii],''),
		[Linia1Stanga],
		rd.[CodUnitate],
		[ValoareLei],
		1,'',@IDResponsabil,GETDATE(),'',[OrdonatorCredite],0
      FROM [RulajeDoc] rd JOIN ExBugSetup ebs ON rd.CodUnitate = ebs.CodUnitate
       JOIN BalconSetup bcs ON bcs.CodUnitate = rd.CodUnitate
			 WHERE [IDDocument]=@IDDocument 
           
	SET @IDAB= SCOPE_IDENTITY()

	INSERT INTO [AngajamentBugetarDetaliu]
           ([IDAB]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[Clasif])
     SELECT @IDAB,sd.IDJurnal,dbo.GetArticolAliniat(ContDebit) ArtAl ,
     rl.valoareLei,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND ((rl.ContDebit like ContAB +'%') OR (rl.ContCredit like ContAB +'%'))

		
		         
	GOTO Gata
Eroare:
	 RAISERROR ('Nu a fost definit responsabilul pentru viza CFP1',16,-1)
Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_GetObiecteInventar]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_GetObiecteInventar]
GO


CREATE PROCEDURE [dbo].[amix_GetObiecteInventar]
      @CodUnitate smallint,
      @FiltruDep varchar(8000),
      @FiltruCont varchar(8000),
      @FiltruSursaFinantare varchar(8000),
      @Luna smallint,
      @Anul smallint
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @PerCurenta decimal(10)
      SELECT @PerCurenta = AnulCurent*100+LunaCurenta FROM AmixSetup WHERE CodUnitate=@CodUnitate
      DECLARE @Arhiva bit

      SET @Arhiva=0
      
      IF @PerCurenta>@Anul*100+@Luna
            SET @Arhiva= 1
            
            
    IF @Arhiva=1
    BEGIN
            PRINT 'Arhiva'
            SELECT ar.[IDMijlocFix]
			  ,ar.[NrInventar]
			  ,ar.[ContAmortizare]
			  ,ar.[StocFaptic]
			  ,ar.[IDClasificare]
			  ,ar.[DataIntrare]
			  ,ar.[DataIesire]
			  ,ar.[CodUnitate]
			  ,ar.[IDDepartament]
			  ,ar.[IDStare]
			  ,ar.[ContImobilizare]
			  ,ar.[ContCheltuiala]
			  ,ar.[DurataAmortizare]
			  ,ar.[RegimAmortizare]
			  ,ar.[ValoareInventar]
			  ,ar.[PunereInFunctiune]
			  ,ar.[Compus]
			  ,ar.[Blocat]
			  ,ar.[IDGestionar]
			  ,ar.[ContTransfer]
			  ,ar.[ValoareAmortizata]
			  ,ar.[LuniAmortizate]
			  ,ar.[AmortizareLuna]
			  ,ar.[AmortizareAn]
			  ,ar.[InFolosinta]
			  ,ar.[Categorie]
			  ,ar.[IDUnitateMasura]
			  ,ar.[ContTransferForSuperior]
			  ,ar.[IDJurnal]
			  ,ar.[ValoareIntrataTransfer]
			  ,ar.[CantitateOI]
			  ,ar.[Denumire]
			  ,ar.[IDSursaFinantare]
			,um.Prescurtare UMDenumire,dp.Nume LocDepozitare,dp2.Nume NumeGestiune FROM AmixRegistruBK ar 
            LEFT OUTER JOIN UnitatiMasura um ON ar.IDUnitateMasura = um.IDUnitateMasura
            LEFT OUTER JOIN Departamente dp ON ar.IDDepartament=dp.IDDepartament AND dp.CodUnitate = ar.CodUnitate
            LEFT OUTER JOIN   Departamente dp2 ON dp.IDDepartParinte = dp2.IDDepartament AND dp.CodUnitate = dp2.CodUnitate
            Where ar.CodUnitate = @CodUnitate AND ar.Compus >=3 
            AND
            (LEN(LTRIM(RTRIM(@FiltruDep)))=0 OR (ar.IDDepartament IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruDep,',') fart))) AND
            (LEN(LTRIM(RTRIM(@FiltruCont)))=0 OR (COALESCE(ar.ContTransferForSuperior,-1) IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruCont,',') fart))) aND
            (LEN(LTRIM(RTRIM(@FiltruSursaFinantare)))=0 OR (COALESCE(ar.IDSursaFinantare,-1) IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruSursaFinantare,',') fart)))
            AND (YEAR([DataIntrare])*100 + MONTH([DataIntrare])) <= (@Anul*100 + @Luna)
            AND Luna=@Luna AND An=@anul
            AND ar.InFolosinta=1
            RETURN
    END
    -- Insert statements for procedure here
      SELECT ar.[IDMijlocFix]
			  ,ar.[NrInventar]
			  ,ar.[ContAmortizare]
			  ,ar.[StocFaptic]
			  ,ar.[IDClasificare]
			  ,ar.[DataIntrare]
			  ,ar.[DataIesire]
			  ,ar.[CodUnitate]
			  ,ar.[IDDepartament]
			  ,ar.[IDStare]
			  ,ar.[ContImobilizare]
			  ,ar.[ContCheltuiala]
			  ,ar.[DurataAmortizare]
			  ,ar.[RegimAmortizare]
			  ,ar.[ValoareInventar]
			  ,ar.[PunereInFunctiune]
			  ,ar.[Compus]
			  ,ar.[Blocat]
			  ,ar.[IDGestionar]
			  ,ar.[ContTransfer]
			  ,ar.[ValoareAmortizata]
			  ,ar.[LuniAmortizate]
			  ,ar.[AmortizareLuna]
			  ,ar.[AmortizareAn]
			  ,ar.[InFolosinta]
			  ,ar.[Categorie]
			  ,ar.[IDUnitateMasura]
			  ,ar.[ContTransferForSuperior]
			  ,ar.[IDJurnal]
			  ,ar.[ValoareIntrataTransfer]
			  ,ar.[CantitateOI]
			  ,ar.[Denumire]
			  ,ar.[IDSursaFinantare],um.Prescurtare UMDenumire,dp.Nume LocDepozitare,dp2.Nume NumeGestiune FROM AmixRegistru ar 
      LEFT OUTER JOIN UnitatiMasura um ON ar.IDUnitateMasura = um.IDUnitateMasura
      LEFT OUTER JOIN Departamente dp ON ar.IDDepartament=dp.IDDepartament AND dp.CodUnitate = ar.CodUnitate
      LEFT OUTER JOIN   Departamente dp2 ON dp.IDDepartParinte = dp2.IDDepartament AND dp.CodUnitate = dp2.CodUnitate
      Where ar.CodUnitate = @CodUnitate AND ar.Compus >=3 AND
      (LEN(LTRIM(RTRIM(@FiltruDep)))=0 OR (ar.IDDepartament IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruDep,',') fart))) AND
      (LEN(LTRIM(RTRIM(@FiltruCont)))=0 OR (COALESCE(ar.ContTransferForSuperior,-1) IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruCont,',') fart))) aND
      (LEN(LTRIM(RTRIM(@FiltruSursaFinantare)))=0 OR (COALESCE(ar.IDSursaFinantare,-1) IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruSursaFinantare,',') fart)))
      AND (YEAR([DataIntrare])*100 + MONTH([DataIntrare])) <= (@Anul*100 + @Luna)
    AND ar.InFolosinta=1
END


GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcDisponibilPAC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CalcDisponibilPAC]
GO

CREATE PROCEDURE [dbo].[CalcDisponibilPAC]
      @CodUnitate smallint,
      @IDPAC int,
      @Data datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAprob]= 
      (SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate
      WHERE (IDDocument IN 
       (SELECT IDDocument FROM RulajeDoc rd  WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContDebit LIKE LTRIM(RTRIM(es.ContBA)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END )
        -(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN 
        (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContCredit LIKE LTRIM(RTRIM(es.ContBA)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END)
            WHERE [IDPac]=@IDPAC

      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAngaj]= (SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] 
      r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<@Data)) AND ContDebit LIKE LTRIM(RTRIM(es.ContAL)) +'_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END)
            -(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<@Data)) AND ContCredit LIKE LTRIM(RTRIM(es.ContAL)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END) 
            --+(SELECT COALESCE(sum(Suma),0) FROM [PropunereAngajareCheltuialaDetaliu] a JOIN [PropunereAngajareCheltuiala] b ON a.IDPac=b.IDPAC  WHERE a.[IDPACDetaliu] < [PropunereAngajareCheltuialaDetaliu].[IDPACDetaliu] AND a.ArtAl = [PropunereAngajareCheltuialaDetaliu].[ArtAl]
            --AND b.Data < @Data)
            WHERE [IDPac]=@IDPAC
            
            
      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [Clasif]=LTRIM(RTRIM(j.[Codificare]))+'.' +dbo.GetTitlu([ArtAl])+'.' + LTRIM(RTRIM([ArtAl]))  FROM [Jurnale] j WHERE j.[IDJurnal] = [PropunereAngajareCheltuialaDetaliu] .[IDJurnal] AND [IDPAC] =@IDPAC
END



GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_ChangeCont]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_ChangeCont]
GO


-- =============================================
-- Author:		Popa Dorin
-- Create date: 08.04.2014
-- Description:	schimba numele contuui
-- =============================================
CREATE PROCEDURE [dbo].[balcon_ChangeCont] 
	@OldCont dbo.TipCont, 
	@NewCont dbo.TipCont,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRAN
    -- Dezactiveaza Triggerii
	ALTER TABLE dbo.Conturi NOCHECK CONSTRAINT FK_Conturi_Conturi
	ALTER TABLE dbo.Rulaje NOCHECK CONSTRAINT FK_Rulaje_ConturiDebit
	ALTER TABLE dbo.Rulaje NOCHECK CONSTRAINT FK_Rulaje_ConturiCredit
	ALTER TABLE dbo.ConturiCoresp NOCHECK CONSTRAINT FK_ConturiCoresp_Conturi
	ALTER TABLE dbo.ConturiCoresp NOCHECK CONSTRAINT FK_ConturiCoresp_Conturi1
	ALTER TABLE dbo.Conturispeciale NOCHECK CONSTRAINT FK_ConturiSpeciale_Conturi

	UPDATE Rulaje SET ContDebit = @NewCont WHERE ContDebit = @OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE	
	UPDATE Rulaje SET ContCredit = @NewCont WHERE ContCredit = @OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE	
	UPDATE Conturi SET ContParinte = @NewCont WHERE ContParinte=@OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE	
	UPDATE ConturiCoresp SET ContDebit=@NewCont WHERE ContDebit =@OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE	
	UPDATE ConturiCoresp SET ContCredit=@NewCont WHERE ContCredit =@OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE	
	UPDATE ConturiSpeciale SET Cont = @NewCont WHERE Cont=@OldCont AND CodUnitate=@CodUnitate
	IF @@ERROR != 0 GOTO EROARE
	IF NOT EXISTS(SELECT * FROM Conturi WHERE CodUnitate=@CodUnitate and Cont=@NewCont)	
		UPDATE Conturi SET Cont = @NewCont WHERE Cont=@OldCont AND CodUnitate=@CodUnitate
	ELSE
		DELETE Conturi WHERE Cont=@OldCont AND CodUnitate=@CodUnitate
	
	IF @@ERROR != 0 GOTO EROARE	
	 -- Activeaza Triggerii
	ALTER TABLE dbo.Conturi CHECK CONSTRAINT FK_Conturi_Conturi
	ALTER TABLE dbo.Rulaje CHECK CONSTRAINT FK_Rulaje_ConturiDebit
	ALTER TABLE dbo.Rulaje CHECK CONSTRAINT FK_Rulaje_ConturiCredit
	ALTER TABLE dbo.ConturiCoresp CHECK CONSTRAINT FK_ConturiCoresp_Conturi
	ALTER TABLE dbo.ConturiCoresp CHECK CONSTRAINT FK_ConturiCoresp_Conturi1
	ALTER TABLE dbo.ConturiSpeciale CHECK CONSTRAINT FK_ConturiSpeciale_Conturi

	COMMIT

	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare schimbarea numelui contului!',16,-1)
		GOTO Gata

	Gata:
END

GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimensiuni]'))
DROP VIEW [dbo].[fares_DocumenteDimensiuni]
GO


CREATE VIEW [dbo].[fares_DocumenteDimensiuni]
AS
SELECT     a.IDDocument, a.NrDoc, a.DataDoc, a.CodUnitate, a.IDTipDocument, a.NrContract, a.DataContract, a.Observatii, a.DataScadenta, a.NrDocReceptie, a.DataDocReceptie, dbo.TipDocument.TipOperatie, 
                      dbo.TipDocument.Denumire, c.valoare1, c.valoare2, c.valoare3, c.valoare4, p.Denumire AS DenPartener, dd.ValoareTVA, CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END AS ValoareTotala, dd.CotaTVA, dd.ValoareTotalaCuAdaos, CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - (dd.ValoareTotala * 100) / (dd.CotaTVA + 100) ELSE (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) 
                      - dd.ValoareTotala END AS Adaos, (dd.ValoareTotalaCuAdaos * dd.CotaTVA) / (dd.CotaTVA + 100) AS TVANeexigibil, ar.Denumire AS DenArticol, p.IDPartener, ar.IDArticol, dd.IDRegimFiscal
FROM         dbo.Documente AS a INNER JOIN
                      dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                      dbo.TipDocument ON a.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                      dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                      dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVanzariTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVanzariTVALaIncasare]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzariTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(TVAExigibil,0) ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - Coalesce(BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN TotalValoareTVA  - Coalesce(TVAExigibil,0) ELSE 0 END TVANeExigibila,	
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,	  
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (61) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) OSRSTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (62) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) PISCDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (63) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) PISFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (64,14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143ad,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (65,15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143bc,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (66) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) EALBPSSDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (67) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) LPSFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (68) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN
		(SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
			WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
			GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
			
	   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
		--AND LTRIM(RTRIM(part.AtributFiscal))<>''
		AND doc.CodUnitate=@CodUnitate 
		AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
		
	UNION	---neexigibile mai mici de 90 de zile
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  Coalesce(selPlati.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) -  Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0) - Coalesce(selPlati.TVAExigibil,0) ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    (((YEAR(DATEADD(day,90,doc.DataDoc))<=2013) AND (DATEADD(day,90,doc.DataDoc) > @PanaLaData)) OR (YEAR(DATEADD(day,90,doc.DataDoc))>=2014)) AND										-- dar nu mai vechi de 90 de zile
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

    --CELE de 90 de zile devin exigibile in perioada curenta pentru facturile din 2012,2013
	UNION	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) in (2012,2013) AND                     -- dupa 2012 si in 2013  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData AND		-- dar nu mai vechi de 90 de zile
    YEAR(DATEADD(day,90,doc.DataDoc)) <=2013 AND										-- data dupa 90 de zile sa nu fie in 2014 
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

	
	ORDER BY DataDoc
END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN COALESCE(ValPlatCuTVA,0)> ValoareTotalaSiTVA THEN ValoareTotalaSiTVA ELSE COALESCE(ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE ValPlatCuTVA END ValPlatCuTVA,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
	  CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza 
	END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
      CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END
    ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) 
	THEN Baza - CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza 
	ELSE COALESCE(BazaExigibila,0) END  ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA - 
	 CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal
	LEFT OUTER JOIN
	(SELECT SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	
	   
	WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
	doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
	
	
 UNION
    --toate cele vechi neplatite de 90 de zile
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN Baza - CASE WHEN 
	ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END  - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA -
	CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	 - COALESCE(selPlatianterior.TVAExigibil,0)ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  (@cumparari=1 OR tipd.IDTipDocument in (1)) AND
	  (@cumparari=0 OR tipd.IDTipDocument in (0)) AND  -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (@cumparari=1 OR (((YEAR(DATEADD(day,90,doc.DataDoc))<=2013) AND (DATEADD(day,90,doc.DataDoc) > @PanaLaData)) OR (YEAR(DATEADD(day,90,doc.DataDoc))>=2014))) AND    -- dar nu mai vechi de 90 de zile
	  (@cumparari=0 OR doc.IDRegimFiscal IN (4,17,18,19,27)) AND         -- cu regim fiscal cu TVA interne
	  (@cumparari=1 OR doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	

 UNION
    --jurnal vanzari care devin exigibile in perioada curenta
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	Baza - COALESCE(selPlatiAnterior.BazaExigibila,0) BazaExigibila,  
	ValoareTVA - COALESCE(selPlatianterior.TVAExigibil,0)	TVAExigibil, 
	0 BazaNeexigibila,  
	0 TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  @cumparari=0 AND								--doar pentru jurnalul de vanzari
	  YEAR(doc.DataDoc) in (2012,2013) AND                     -- dupa 2012 
	  (tipd.IDTipDocument in (1)) AND
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData) AND    -- care devin exigibile le 90 de zile
	   YEAR(DATEADD(day,90,doc.DataDoc)) <=2013 AND
	  (doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	


	
   ORDER BY DataDoc
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[write_log]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[write_log]
GO



CREATE PROCEDURE [dbo].[write_log]
	@SqlCommand varchar(8000),
	@SqlParam varchar(8000),
	@ErrMessage varchar(8000),
	@idLog bigint OUT
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @now datetime
	DECLARE @cmd varchar(8000)
	DECLARE @an varchar(4)
	DECLARE @luna varchar(2)
	DECLARE @numetabela varchar(200)
	SET @now = GETDATE()
	SET @an =  CAST(YEAR(@now) as varchar(4));
	SET @luna = LTRIM(RTRIM(CAST(MONTH(@now) as varchar(2))));

	IF LEN(@luna)=1
		SET @Luna = '0' + @luna;

	SET @numetabela  = N'[ESRP_log].[dbo].[log' +@an  + @luna + ']'
	
    -- Insert statements for procedure here
	IF NOT EXISTS (SELECT * FROM [ESRP_log].sys.objects WHERE object_id = OBJECT_ID(@numetabela)  AND type in (N'U'))
	BEGIN
		SET @cmd ='USE [ESRP_log];
		CREATE TABLE '+@numetabela +'(
			[ID] [bigint] IDENTITY(1,1) NOT NULL,
			[DataExecutie] [datetime] NOT NULL,
			[SQLCommand] [varchar](8000) NOT NULL,
			[SQLParam] [varchar](8000) NOT NULL,
			[UserName] [varchar](50) NOT NULL,
			[IPAddress] [varchar](200) NOT NULL,
			[Result] [int] NOT NULL,
			[ErrorMessage] [varchar](8000) NOT NULL,
		 CONSTRAINT [PK_log'+@an+@luna+'] PRIMARY KEY CLUSTERED ([ID] ASC)) '
		PRINT(@cmd)
		EXEC(@cmd)
		IF @@ERROR != 0 GOTO EROARE	
	END

	SET @cmd = 'INSERT INTO '+@numetabela +'
           ([DataExecutie]
           ,[SQLCommand]
		   ,[SQLParam]
           ,[UserName]
           ,[IPAddress]
           ,[Result]
           ,[ErrorMessage])
     VALUES
           (GETDATE()
           ,''' +@SqlCommand +'''
		   ,''' +@SqlParam +'''
           ,suser_sname()
           ,host_name()
           ,0
           ,'''+@ErrMessage+''')'
	PRINT(@cmd)
	EXEC(@cmd)

	IF @@ERROR != 0 GOTO EROARE	
	SET @idLog = @@IDENTITY 


	GOTO Gata
	EROARE:
		RAISERROR ('Eroare la salvarea in log datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
END


GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteTVADimensiuni]'))
DROP VIEW [dbo].[fares_DocumenteTVADimensiuni]
GO


CREATE VIEW [dbo].[fares_DocumenteTVADimensiuni]
AS
SELECT     a.IDDocument, a.NrDoc, a.DataDoc, a.CodUnitate, a.IDTipDocument, a.NrContract, a.DataContract, a.Observatii, a.DataScadenta, a.NrDocReceptie, a.DataDocReceptie, dbo.TipDocument.TipOperatie, 
                      dbo.TipDocument.Denumire, c.valoare1, c.valoare2, c.valoare3, c.valoare4, p.Denumire AS DenPartener, CASE WHEN dd.CotaTVA = 24 THEN dd.ValoareTVA ELSE 0 END AS ValoareTVA24, 
                      CASE WHEN dd.CotaTVA = 9 THEN dd.ValoareTVA ELSE 0 END AS ValoareTVA9, CASE WHEN dd.CotaTVA = 24 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotala24, CASE WHEN dd.CotaTVA = 9 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotala9, CASE WHEN dd.CotaTVA = 0 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotalaScutit, dd.CotaTVA, CASE WHEN (dd.CotaTVA = 24 OR
                      dd.CotaTVA = 0) THEN dd.ValoareTotalaCuAdaos ELSE 0 END AS ValoareTotalaCuAdaos24, CASE WHEN dd.CotaTVA = 9 THEN dd.ValoareTotalaCuAdaos ELSE 0 END AS ValoareTotalaCuAdaos9, 
                      CASE WHEN dd.CotaTVA = 0 THEN (dd.ValoareTotalaCuAdaos * 100) / (s.CotaTVAImplicita + 100) - dd.ValoareTotala ELSE CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - (dd.ValoareTotala * 100) / (dd.CotaTVA + 100) ELSE (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) 
                      - dd.ValoareTotala END END AS Adaos, CASE WHEN dd.CotaTVA = 0 THEN (dd.ValoareTotalaCuAdaos * s.CotaTVAImplicita) / (s.CotaTVAImplicita + 100) ELSE (dd.ValoareTotalaCuAdaos * dd.CotaTVA) 
                      / (dd.CotaTVA + 100) END AS TVANeexigibil, ar.Denumire AS DenArticol, p.IDPartener, ar.IDArticol, dd.IDRegimFiscal
FROM         dbo.Documente AS a INNER JOIN
                      dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                      dbo.TipDocument ON a.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                      dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                      dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol INNER JOIN
                      dbo.FaresSetup AS s ON a.CodUnitate = s.CodUnitate

GO

UPDATE [admin_ApplicationMenu] SET [Ordine]=131 WHERE [IDMeniuAplicatie]=9013
GO

UPDATE [admin_ApplicationMenu] SET [Ordine]=132 WHERE [IDMeniuAplicatie]=9014
GO

